# 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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

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

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

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

1,218,942
Messages
6,145,352
Members
450,610
Latest member
TheEnginerd

### 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.

### Which adblocker are you using?

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

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