CODEWITHSUNDEEP
excelsortingexcel-2010vba
WJD
WJD

Reputation: 67

Sort alphanumeric values

I need to sort a list of part numbers alphanumerically. Excel will not sort them correctly though when I open the auto filter drop down it shows them in the order that I need.

They aren't uniform in length, some contain letters, some have leading zeros, with a maximum length of 15 characters. Here is a list along with the correct sorting:

Part       Correct Sort
00863      00863
1123       02257
02257      07706
6549       10338
6834       11212
07706      1123
8001       65412SSCY
8001       6549
10338      6834
11212      8001
65412SSCY  8001
EN93       EN93
SCSM11BE   SCSM11BE

Hoping for a macro or method.

Upvotes: 2

Views: 2318

Answers (3)

dev1998
dev1998

Reputation: 892

Select the column with the original data and then click on "Text to Columns". Pick Delimited as the file type, and then click "Next". Uncheck all the delimiters and then click "Next". On the Step 3 of 3 window, choose Text as the Column data format. Then click Finish.

All your data will now act as type = text.

Then select all the data again, and in the sort you can choose "Sort numbers and numbers stored as text separately"

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You need to specify the correct option to Excel:

enter image description here

and after the sort:

enter image description here

Upvotes: 0

user4039065
user4039065

Reputation:

If you have values formatted as Text and not numbers with custom number formats (for leading zeroes) then all you need to do is choose the correct Sort Warning option.

        Sort numbers as text

If you opt for *Sort numbers and numbers stored as text separately, you will come up with teh correct (as per your sample) sort order.

Note that text by default is left-aligned in a cell while numbers (regardless of their number formatting) are right-aligned by default.

Upvotes: 2

Related Questions