Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text 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

I posted this message here earlier and also posted it to a microsoft
site without fully realising that it is getting duplicated here and
there ......

Sorry :-(
Mike


--
Grandslam90
------------------------------------------------------------------------
Grandslam90's Profile: http://www.excelforum.com/member.php...o&userid=17408
View this thread: http://www.excelforum.com/showthread...hreadid=480087

Mike,

Copy the code below into a codemodule, and use the function like

=ISort(A1)

where A1 has your string to be re-ordered.

HTH,
Bernie
MS Excel MVP

Function ISort(inCell As Range) As String
Dim myVals As Variant
Dim i As Integer

myVals = Split(inCell.Value, " ")

BubbleSort myVals

For i = LBound(myVals) To UBound(myVals)
ISort = ISort & myVals(i) & " "
Next i

ISort = Trim(ISort)

End Function

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As Variant

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i

End Function
"Mike S" <Mike wrote in message
...
Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text 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

On Fri, 28 Oct 2005 04:12:03 -0700, Mike S <Mike
wrote:

Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text 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


Try this:

=================================
Option Explicit

Function foo(str As String) As String
Dim Temp

Temp = Split(str)
Temp = SingleBubbleSort(Temp)

foo = Join(Temp)
End Function

Private Function SingleBubbleSort(TempArray As Variant)
'copied from support.microsoft.com
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 0 To UBound(TempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) TempArray(i + 1) Then
NoExchanges = False
Temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = Temp
End If
Next i
Loop While Not (NoExchanges)
SingleBubbleSort = TempArray
End Function
==============================


--ron

"Mike S" <Mike wrote...
Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text 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.

Easiest way to do this would be to use add-in functions from Laurent
Longre's MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

If you install MOREFUNC.XLL, try the array formula

=MCONCAT(VSORT(MID(A1,SMALL(IF(MID(" "&A1,INTVECTOR(1024,1),1)="
",INTVECTOR(1024,1)),
INTVECTOR(WORDCOUNT(A1),1)),SMALL(IF(MID(A1&" ",INTVECTOR(1024,1),1)=" ",
INTVECTOR(1024,1)),INTVECTOR(WORDCOUNT(A1),1))-SMALL(IF(MID(" "&A1,
INTVECTOR(1024,1),1)="
",INTVECTOR(1024,1)),INTVECTOR(WORDCOUNT(A1),1))), ,1)," ")

or use a defined name like seq referring to =ROW(INDIRECT("1:1024")), which
would allow shortening the formula to

=MCONCAT(VSORT(MID(A1,SMALL(IF(MID(" "&A1,seq,1)="
",seq),INTVECTOR(WORDCOUNT(A1),1)),
SMALL(IF(MID(A1&" ",seq,1)=" ",seq),INTVECTOR(WORDCOUNT(A1),1))
-SMALL(IF(MID(" "&A1,seq,1)=" ",seq),INTVECTOR(WORDCOUNT(A1),1))),,1)," ")