How can I make my UDF extract return words from string in alphabetical order? (Excel 2007)

[Nakarin]

Hi thanks for reading my post.

I have written a UDF (well borrowed it from somewhere, probably this site) that extracts alpha strings longer than 3 letters long and discards numbers and other characters. For example from:

EUR 010210-280210 CAR RENTAL

it will return:

EUR,CAR,RENTAL

However I would like to modify it so that the words in the string it returns are always listed in alphabetical order, i.e. CAR,EUR,RENTAL, to aid normalisation of multiple descriptions where the words happen to be in a different order.

Any thoughts on how to solve this challenge much appreciated. I have included the original UDF below.

Many thanks

Nick

Function Xtractalpha3(ByVal ref As String) As String
Dim rx As Object, arr As Object, i As Integer
Set rx = CreateObject("VBscript.Regexp")
With rx
.Pattern = "\b[A-Za-z][A-Za-z][A-Za-z]*\b"
.Global = True
If .test(ref) Then Set arr = .Execute(ref)
End With
For i = 0 To arr.Count - 1
Xtractalpha3 = Xtractalpha3 & "," & arr(i)
Next
Xtractalpha3 = Replace(Xtractalpha3, ",", "", 1, 1)
End Function

 

[Krishnakumar]

Hi,

Function Xtractalpha3(ByVal ref As String) As String
    Dim rx As Object, arr As Object, i As Integer
    
    Set rx = CreateObject("VBscript.Regexp")
    
    With rx
        .Pattern = "\b[A-Za-z][A-Za-z][A-Za-z]*\b"
        .Global = True
        If .test(ref) Then Set arr = .Execute(ref)
    End With
    For i = 0 To arr.Count - 1
        Xtractalpha3 = Xtractalpha3 & "," & arr(i)
    Next
    Xtractalpha3 = SortThis(Replace(Xtractalpha3, ",", "", 1, 1))
End Function
Private Function SortThis(ByVal a)
    Dim i   As Long, j As Long, t, x
    x = Split(a, ",")
    For i = LBound(x) To UBound(x)
        For j = i To UBound(x)
            If x(i) > x(j) Then
                t = x(i)
                x(i) = x(j)
                x(j) = t
            End If
        Next
    Next
    SortThis = Join(x, ",")
End Function

HTH

 

[Nakarin]

Kris - fantastic! works a treat

TVM!