Sort Ranges That Contain Text And Numbers In ASCII Sequence
For the most advanced techniques in sorting and searching, Rich's newest book provides a bonus app that provides the code to enhance your most demanding applications.
Power-Up Using Excel VBA Sorts and Searches
Some of the key Excel commands, such as VLOOKUP and MATCH, require that a range of data (which is one of their parameters) be sorted in ASCII sequence.
What happens if you have a range (i.e. column) of data with a mixture of numberes and text? The standard Excel sort will not sort them into pure ASCII order, and the VLOOKUP and MATCH commands will not function correctly. To properly sort this type of data, you must first convert all numbers into text. Simply selecting the range, converting the cell formats to Text, and then resorting will NOT solve this issue. You must actually convert the numbers into text after the cell format has been changed to text.
The program code illustrates how to perform this transformation and then sort the range into ASCII sequence. Note that there are a few exceptional characters which are not sorted in absolute ASCII sequence (Apostrophes (') and hyphens (-) are ignored). If you sort data with those two characters, the result will possibly not be in true ASCII order; however, this is not an issue because the VLOOKUP and MATCH commands take those exceptions into consideration when searching.
To allow Excel VBA sequence check programs to work with apostrophes and hyphens, you MUST use the OPTION COMPARE TEXT statement at the top of your VBA code. It will then sequence check properly those files sorted by Excel.
If you omit the option or select OPTION COMPARE BINARY, the code will show it out of sequence.
Program Code
Option Explicit
Sub SortAsASCIIText()
' ***************************************************************
' Convert All Numbers to Text And Sort In ASCII Order
' For This Example, Only Column A Is Being Processed
' ***************************************************************
Dim lngLastRow As Long
Dim rngConvertToText As Range
Dim C As Range
Dim strTemp As String
' ***************************************************************
' Change The Cell Format to Text
' ***************************************************************
Columns("A:A").Select
Selection.NumberFormat = "@"
' ***************************************************************
' Count The Number of Rows To Sort
' ***************************************************************
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
' ***************************************************************
' Set A Range Variable For Sorting
' ***************************************************************
Set rngConvertToText = Range("A1:A" & lngLastRow)
' ***************************************************************
' Copy The Number To A String Variable And Then Copy Back To Cell
' This Will Transform The Number To a Text Format
' ***************************************************************
For Each C In rngConvertToText
strTemp = C.Value
C.Value = strTemp
Next C
' ***************************************************************
' Sort The Range in ASCII Order
' ***************************************************************
rngConvertToText.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub