# Sort Alphanumeric Text

## Sort Cells In Excel With Text & Numbers

Got any Excel Questions? Excel Help .

See Normal Excel Sort and Extract Numbers From Text

Sorting Alphanumeric

Excel has a problem trying to sort alphanumeric cells in cells by the number portion only. The reason is simply because Excels Sort feature evaluates each cell value by reading left to right. However, we can over-come this in a few ways with the aid of Excel Macros .

Fixed Length Text

If the alphanumeric cells are all a fixed length we can simply use another column to extract out the numeric portion of the alphanumeric text and then sort by the new column. For example, say we can alphanumeric text in Column A like ABC196, FRH564 etc. We can simply add the formula below to Column B.

=--RIGHT(A1,3)

OR

=--Left(A1,3) for fixed length alphanumeric text like 196GFT

OR

=--MID(A1,5,4) for alphanumeric text like a-bg1290rqty where you know the number Start s at the 5th character and has 4 numbers

Then Fill Down as far as needed. Then we can select Column B, copy and Edit>Paste Special - Values. Next we sort Columns A & B by Column B and then delete Column B.

NOTE: the double negative (--) ensures the number returned is seen as a true number.

Sort Alphanumeric

Any Length Alphanumeric Text

A problem comes about when the numeric portion and/or the text portion can be any length. In these cases a macro is best. The code below should be copied to any standard Module (Insert>Module). Then simply run the SortAlphaNumerics Procedure.

It should be noted that the ExtractNumber Function has 2 optional arguments (Take_decimal and Take_negative). These are both False if omitted. See the table below to see how alphanumeric text is treated.

 Alphanumeric Text Formula Result a-bg-12909- =ExtractNumber(A1,,TRUE) -12909 a-bg-12909- =ExtractNumber(A2) 12909 a.a1.2... =ExtractNumber(A3,TRUE) 1.2 a.a1.2... =ExtractNumber(A4) 12 a.a-1.2.... =ExtractNumber(A5,TRUE,TRUE) -1.2 abg1290.11 =ExtractNumber(A6,TRUE) 1290.11 abg129013Agt =ExtractNumber(A7) 129013 abg129012 =ExtractNumber(A8) 129013

Alphanumeric Sorting Code

```'MUST be at top of same public module housing _

Sub SortAlphaNumerics and Function ExtractNumber

Dim bDec As Boolean, bNeg As Boolean

Sub SortAlphaNumerics()

Dim wSheetTemp As Worksheet

Dim wsStart As Worksheet

Dim lLastRow As Long, lReply As Long

Dim rSort As Range

''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid Business Applications

'www.ozgrid.com

'Sorts Alphanumerics Of Column "A" of active Sheet.

'ExtractNumber Function REQUIRED

'http://www.ozgrid.com/VBA/ExtractNum.htm

''''''''''''''''''''''''''''''''''''''''''

Set wsStart = ActiveSheet

On Error Resume Next

Set rSort = Application.InputBox _

("Select range to sort. Any heading should be bolded and included", "ALPHANUMERIC SORT", _
ActiveCell.CurrentRegion.Columns(1).Address, , , , , 8)

If rSort Is Nothing Then Exit Sub

If rSort.Columns.Count > 1 Then

MsgBox "Single Column Only"

SortAlphaNumerics

End If

'Application.ScreenUpdating = False

Set rSort = Range(Cells(rSort.Cells(1, 1).Row, rSort.Column), _
Cells(Rows.Count, rSort.Column).End(xlUp))

lReply = MsgBox("Include Decimals within numbers", vbYesNoCancel, "OZGRID ALPHANUMERIC SORT")

If lReply = vbCancel Then Exit Sub

bDec = lReply = vbYes

lReply = MsgBox("Include negative signs within numbers", vbYesNoCancel, "OZGRID ALPHANUMERIC SORT")

If lReply = vbCancel Then Exit Sub

bNeg = lReply = vbYes

lLastRow = rSort.Cells(rSort.Rows.Count).Row

Set wSheetTemp = Worksheets.Add

rSort.Copy wSheetTemp.Range("A1")

With wSheetTemp.Range("B1:B" & lLastRow)

.FormulaR1C1 = "=ExtractNumber(RC[-1]," & bDec & "," & bNeg & ")"

.Copy

.PasteSpecial xlPasteValues

Application.CutCopyMode = False

End With

bNeg = False

bDec = False

With wSheetTemp.UsedRange

.Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.Columns(1).Cut wsStart .Range(rSort.Cells(1, 1).Address)

End With

wSheetTemp.Delete

Application.ScreenUpdating = True

End Sub

Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double

Dim iCount As Integer, i As Integer, iLoop As Integer

Dim sText As String, strNeg As String, strDec As String

Dim lNum As String

Dim vVal, vVal2

''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid Business Applications

'www.ozgrid.com

'Extracts a number from a cell containing text and numbers.

''''''''''''''''''''''''''''''''''''''''''

sText = rCell

If Take_decimal = True And Take_negative = True Then

strNeg = "-" 'Negative Sign MUST be before 1st number.

strDec = "."

ElseIf Take_decimal = True And Take_negative = False Then

strNeg = vbNullString

strDec = "."

ElseIf Take_decimal = False And Take_negative = True Then

strNeg = "-"

strDec = vbNullString

End If

iLoop = Len(sText)

For iCount = iLoop To 1 Step -1

vVal = Mid(sText, iCount, 1)

If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then

i = i + 1

lNum = Mid(sText, iCount, 1) & lNum

If IsNumeric(lNum) Then

If CDbl(lNum) < 0 Then Exit For

Else

lNum = Replace(lNum, Left(lNum, 1), "", , 1)

End If

End If

If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))

Next iCount

ExtractNumber = CDbl(lNum)

End Function```

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

# Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With \$139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft