# Alphanumeric Logical Sorting

#### wizkid

##### New Member
I've a long list of data (1500 cells) in the following format:

AK1
AK2
BL1
BL100
BL2
BL173
WSB1
WSB20
NK1
NK100
NK40

I need to sort this data so that the 'alpha' part (upto 3 char) is in alphabetic order and 'numeric' part (upto 3 char) is in logical number sequence and the result is displayed in the same cell. as below.

BL1
BL2
BL100
NK1
NK40
NK100 AND SO ON.

I've tried to find the solution from excel formulas and even asked for professional help.but no success achieved.

Someone pl.Help.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Richard Schollar

##### MrExcel MVP
Hi

If you're prepared to use two helper columns and a UDF then it's a breeze - post tthe following code into a standard module in the workbook holding this data:

Code:
``````Function GetChar(txt As String, Optional TextOrNum As Integer = 0) As Variant
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
If TextOrNum = 0 Then
With regex
.Pattern = "\d+"
.Global = True
GetChar = .Replace(txt, "")
End With
Else
With regex
.Pattern = "\D+"
.Global = True
GetChar = .Replace(txt, "") + 0
End With
End If
End Function``````

Then in an adjacent column, use the UDF like so:

=getchar(A1,0)

to return the alpha characters (eg ABC), and then in the next avaialbale column use:

=getchar(A1,1)

to return the numeric characters.

Then select the entire range (including the helper columns) and sort by the alpha then by the numeric characters.

Richard

#### pgc01

##### MrExcel MVP
Hi wizkid
Welcome to the board

You can also use a formula.
If your data is in column A write in B1

Code:
``=LEFT(A1,MIN(-1+FIND(ROW(\$1:\$10)-1,A1&"0123456789")))&TEXT(RIGHT(A1,LEN(A1)-MIN(-1+FIND(ROW(\$1:\$10)-1,A1&"0123456789"))),"000")``
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down till the end of the data

Now select columns A:B and sort by B

What the formula does is fomat the number part with 3 digits so that the sorting works with no problem, like:

Code:
``````NK1     -->  NK001
NK100  -->  NK100
NK40    -->  NK040``````

Hope this helps
PGC

#### wizkid

##### New Member
Dear 'PGC'
Thnx a zillion for the prompt help.ur solution almost solved my prob.only thing is that i want the result to be displayed without zeroes.ie NK40 should not be displayed as NK040.pl suggest any changes in the formula.
THNX Again

#### Richard Schollar

##### MrExcel MVP
PGC01's formula is only meant to exist as a "helper column" for you to sort the original column as required. Say your original data is in ColA, and you input PGC01's formula in ColB, then if you select both columns A&B and click on sort, if you select to sort ascending on column B, then column A will be sorted as you originally wanted. You can delete or hide column B if you do not want it visible.

Richard