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 |
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 |
All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com