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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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,887
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,832
Messages
5,855,890
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top