Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Alphabetically reorder a text string with multiple words
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 -- Grandslam90 ------------------------------------------------------------------------ Grandslam90's Profile: http://www.excelforum.com/member.php...o&userid=17408 View this thread: http://www.excelforum.com/showthread...hreadid=480076 |
#2
|
|||
|
|||
Alphabetically reorder a text string with multiple words
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), " ", _ bCompa=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. In article , Grandslam90 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
multiple text files URGENT | Excel Discussion (Misc queries) | |||
importing multiple text files??? | Excel Discussion (Misc queries) |