Sort chars in string

SteveWright

New Member
Joined
Aug 26, 2009
Messages
44
Hi guys.

I need a function that sorts a string from 1 cell into alphabetical order.

i.e. if A1 = "cba" then B1 function would return abc.

Any ideas?

Many thanks,

Steve
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try this custom function:

Code:
Function SortIt(strIn As String)
    Dim i As Long, j As Long
    Dim Temp As String
    SortIt = strIn
    For i = 1 To Len(SortIt) - 1
        For j = i + 1 To Len(SortIt)
            If Mid(SortIt, i, 1) > Mid(SortIt, j, 1) Then
                Temp = Mid(SortIt, i, 1)
                SortIt = WorksheetFunction.Replace(SortIt, i, 1, Mid(SortIt, j, 1))
                SortIt = WorksheetFunction.Replace(SortIt, j, 1, Temp)
            End If
        Next j
    Next i
End Function

Use it on a worksheet like this:

=SortIt(A1)
 
Upvote 0
While mine isn't as slick (temporarily dumps string into an array), it does have a 'case sensitivity' flag

Code:
Public Function sortChars(ByVal str As String, Optional cs As Boolean = False) As String
    Dim a() As Variant
    ReDim a(Len(str))
    For i = 1 To Len(str)
        a(i) = Mid(str, i, 1)
    Next
    For x = 1 To Len(str) - 1
        For y = x To Len(str)
            If cs Then
                c = a(y) < a(x)
            Else
                c = LCase(a(y)) < LCase(a(x))
            End If
            If c Then
                t = a(x)
                a(x) = a(y)
                a(y) = t
            End If
        Next y
    Next x
    sortChars = ""
    For i = 1 To Len(str)
        sortChars = sortChars & a(i)
    Next
End Function

=sortchars(A1,TRUE) is case sensitive

=sortchars(A1,FALSE) or =sortchars(A1) is not
 
Upvote 0
Thanks guys.

I have created a UDF which does this now but will try yours too as they will probably be faster.
 
Upvote 0
Just out of interest, I seem to recall that commands like

Code:
SortIt = WorksheetFunction.Replace(SortIt, i, 1, Mid(SortIt, j, 1))

can be replaced with

Code:
mid$(SortIt, i, 1) = Mid$(SortIt, j, 1)

If so, this might further help speedwise; though I don't have Excel to hand, so I can't try this out...
 
Upvote 0
Code:
Function SortedString(aString As String, Optional Descending As Boolean = False)
    Dim Pivot As String, aChr As String, i As Long
    Dim strLeft As String, strRight As String
    
    If Len(aString) <= 1 Then
        SortedString = aString
    Else
        Pivot = Mid(aString, 1, 1)

        For i = 2 To Len(aString)
            aChr = Mid(aString, i, 1)
            If (aChr < Pivot) Xor Descending Then
                strLeft = strLeft & aChr
            Else
                strRight = aChr & strRight
            End If
        Next i

        SortedString = SortedString(strLeft, Descending) & Pivot & SortedString(strRight, Descending)

    End If
    
End Function

Sub Test()
    MsgBox SortedString("alphA")
    MsgBox SortedString("alphA", True)
End Sub
 
Last edited:
Upvote 0
Just out of interest, I seem to recall that commands like

Code:
SortIt = WorksheetFunction.Replace(SortIt, i, 1, Mid(SortIt, j, 1))

can be replaced with

Code:
mid$(SortIt, i, 1) = Mid$(SortIt, j, 1)

If so, this might further help speedwise; though I don't have Excel to hand, so I can't try this out...

It would be nice if you could do that but it doesn't work. Your expression is one of equality rather than assignment.
 
Upvote 0
I obviously recalled wrongly! I suppose you'd have to go the byte array route to assign directly to a given location then. Pity...
 
Upvote 0
The Mid statement, which is different than the Mid function, works as described on my Excel 2004.
 
Upvote 0

Forum statistics

Threads
1,215,772
Messages
6,126,812
Members
449,339
Latest member
Cap N

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top