How to Alphabetize a Text String in Excel
To create alphagrams in Excel, you need to be able to alphabetize the letters in a text string (or word). For example, the alphagram of ANEROID is ADEINOR. If you have a couple words, then doing this by hand isn’t a big deal. If you have a list of a thousand words, then you need to automate it. Excel doesn’t have a built-in function that alphatizes text strings (as far as I know), so you need to set up a custom function.
I’m doing this in Excel 2007. The code I’m using isn’t mine. I grabbed it from another website 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 window (it will automatically save your changes).
6) You now have a custom function called SORTLETTERS that you can use like any other function in your Excel document. For example, if you type “=sortletters(C1)” in a cell, the letters in the text string in C1 will be returned in alphabetical order. If you have a list, just enter the function once, then use the fill feature like you would with any other function.