Sort numbers with text in ascending order

[Tvouk]

I am having a difficult time sorting the following list:

MY List How Excel Sorts I Need
2148-00 1472-00 171B-00
1480C-00 1480B-00 171C-00
1480B-00 1480C-00 1472-00
1472-00 171B-00 2148-00
171C-00 171C-00 1480B-00
171B-00 2148-00 1480C-00


I have tried formatting numbers as text and other options. I am at my
wits end now. Is this sort even possible? Please help.

Tania

 

[John P]

TVOUK

Where is the logic to your sort please?
If you can identify that then a sort may be possible! It should be numeric
or alphabetical somewhere within the string.


John

 

[Dave Peterson]

Any chance that you made a mistake in your message and wanted the 2148-00 sorted
to the bottom?

171B-00
171C-00
1472-00
1480B-00
1480C-00
2148-00

If that's true, then I'd insert 3 helper columns to be used to extract the
leading numeric portion from the trailing text.

Say your data was in A1:A???
In B1, put this:
=MATCH(TRUE,(ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)
but hit ctrl-shift-enter instead of just enter.
For 171B-00,it returns a 4 (first position of a non-number)

In C1, put this:
=--LEFT(A1,B1-1)
it just returns 171 (as a number)

In D1, put this:
=MID(A1,B1,LEN(A1))
It returns B-00

Now select your range A1???

And sort first by column C, then by column D.

 

[Tvouk]

Thank you sooooooooooooooooooo much!!!!! It worked!!!!!!!!

I really appreciate your help!!!!!

Tania

 

[Dave Peterson]

Woohoo!

Thank you sooooooooooooooooooo much!!!!! It worked!!!!!!!!

I really appreciate your help!!!!!

Tania

<<snipped>>