How to Alphabetize a Text String in Excel

To cre­ate alpha­grams in Excel, you need to be able to alpha­bet­ize the let­ters in a text string (or word).  For exam­ple, the alpha­gram of ANEROID is ADEINOR.  If you have a cou­ple words, then doing this by hand isn’t a big deal.  If you have a list of a thou­sand words, then you need to auto­mate it.  Excel doesn’t have a built-in func­tion that alpha­tizes text strings (as far as I know), so you need to set up a cus­tom function.

I’m doing this in Excel 2007.  The code I’m using isn’t mine.  I grabbed it from another web­site a while back.  Here’s how to do it:

1) Open up Excel.

2) Hit Alt-F11 (to bring up Visual Basic).

3) Go to INSERT at the top, then select MODULE in the drop-down menu.

4) Past in this code:

Function SortLetters(v As Variant) As String
Dim bFlag As Boolean
Dim n As Long, i As Long
Dim s As String

   n = Len(v)
   If n = 0 Then Exit Function

   ReDim arrS(0 To Len(v))
   For i = 1 To Len(v)
       arrS(i) = Mid$(v, i, 1)
   Next

   Do
       bFlag = True
       For i = LBound(arrS) To UBound(arrS) — 1
           If arrS(i) > arrS(i + 1) Then
               bFlag = False
               arrS(0) = arrS(i)
               arrS(i) = arrS(i + 1)
               arrS(i + 1) = arrS(0)
           End If
       Next i
   Loop While Not bFlag

   s = ““
   For i = 1 To UBound(arrS)
       s = s & arrS(i)
   Next

   SortLetters = s

End Function

5) Close the Visual Basic win­dow (it will auto­mat­i­cally save your changes).

6) You now have a cus­tom func­tion called SORTLETTERS that you can use like any other func­tion in your Excel doc­u­ment.  For exam­ple, if you type “=sortletters(C1)” in a cell, the let­ters in the text string in C1 will be returned in alpha­bet­i­cal order.  If you have a list, just enter the func­tion once, then use the fill fea­ture like you would with any other function.

Share on Facebook