Sort House Numbers

[mike gee]

How can I get excel to sort house numbers as I would like, i.e
1,2,2a,3,4,5,5a,5b, et. etc.
So far I've ailed miserably. Any help out there? TIA
Mike G

 

[David McRitchie]

Hi Mike,
You will have to separate the digits into a separate cell.
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
Extract the First Set of Digits (#DigitsFirstID)
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID

 

[Jason Morin]

Use a helper. Say your data is in column A. If your
numbers down go higher than 9, use this in B1:

=LEFT(A1)*1

and fill down. Now select both columns and sort ascending
on column B.

If they are higher than 9, use:

=LEFT(A1,MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN
(A1))),1)*1),ROW(INDIRECT("1:"&LEN(A1))))))*1

and press ctrl/shift/enter before filling down.

HTH
Jason
Atlanta, GA