Alphanumeric Logical Sorting

wizkid

New Member
Joined
Sep 22, 2006
Messages
2
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.
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Apr 25, 2006
Messages
19,870
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
Joined
Sep 22, 2006
Messages
2
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
Joined
Apr 19, 2005
Messages
23,707
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,881
Messages
5,543,008
Members
410,583
Latest member
gazz57
Top