Stupid sorting problem

RonN

New member
Joined
Nov 19, 2012
Messages
3
Reaction score
0
Points
0
I'm forced at work to use Excel 2003 and I have a large database of numbers in a column that I have to use to sort. Other columns will be sorted as well. The sort column contains text and numbers like : 23-A2-006 and 100-A2-007 etc. When I sort, the sorted columns start with 100-A1-011 and 15-M1-004 is far below this after the 150 prefix. The numbers should sort 12-M1-004, 13-M1-004....427-M2-007, but they don't. Can I do anything to get them to sort correctly?

Thanks
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
Hi RonN,

Since your "numbers" contain text, that is how they are sorted. You need to make an adjustment for the missing "0"s for the single or two digit starting numbers. A simple approach would be to use a helper column with the formula:

Code:
=IF(LEN(A2)=8,"00"&A2,IF(LEN(A2)=9,"0"&A2,A2))

Then sort the helper column.
 
Top