Alphanumeric Logical Sorting

[wizkid]

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.

 

[Richard Schollar]

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:

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]

Hi wizkid
Welcome to the board

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

=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:

NK1     -->  NK001
NK100  -->  NK100
NK40    -->  NK040

Hope this helps
PGC

 

[wizkid]

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]

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