Sort Ranges That Contain Text And Numbers In ASCII Sequence
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.
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