Alphabetically reorder a text string with multiple words

[Grandslam90]

Hallo folks,

Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text string with multiple words.

For example.
"Hotel Paris Hilton" should convert to "Hilton Hotel Paris"
"Paris" is then the last word because "H" is before "P" in the
alphabet
"Hilton" is the first word because "i" is before "o" in the Alphabet
and so
on and so forth.

For any help I would be really grateful.
Thanks
Mike

 

[JE McGimpsey]

One way:

Public Function AlphabetizeWords(sInput As String) As Variant
Dim dummy() As String
Dim sArray As Variant
Dim sTemp As String
Dim i As Long
Dim bChanged As Boolean
sArray = Split(Application.Trim(sInput), " ", _
bCompare:=vbTextCompare)
Do
bChanged = False
For i = 1 To UBound(sArray)
If StrComp(sArray(i - 1), sArray(i), 1) = 1 Then
sTemp = sArray(i - 1)
sArray(i - 1) = sArray(i)
sArray(i) = sTemp
bChanged = True
End If
Next i
Loop Until Not bChanged
AlphabetizeWords = Join(sArray, " ")
End Function

Note that the Split and Join functions are VBA6 only, so if the function
needs to run in XL97 or MacXL, you'll have to roll your own functions.