sorting alpha numeric list by first left digit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaduna - 13 Jan 2006 00:50 GMT

I have multiple lists of part numbers, both numeric and alpha numeric in
varying digit numbers and wish to sort them.

Is it not possible to sort a column based on reading the cell from left
to right? ie:001, 111, 2, 22-33, 3, etc.

I have lists that were sorted this way but trace how.

Rgds

Signature

kaduna

Reply to this Message

CLR - 13 Jan 2006 16:36 GMT

It looks like it might just all be TEXT formatted to me, and just sorted in
the normal up/down fashion from the toolbar......

If they are mixed, text and numbers, thenwith a copy of your sheet, try
reformatting the whole column as text and then sorting normally.......

Vaya con Dios,
Chuck, CABGx3

> I have multiple lists of part numbers, both numeric and alpha numeric in
> varying digit numbers and wish to sort them.
[quoted text clipped - 5 lines]
>
> Rgds

Reply to this Message

kaduna - 13 Jan 2006 17:28 GMT

Thanks for the feedback.

Formatting the column as text still gives me the same sort as with
numeric search. ie: shorter numbers are first.

Andy Davidso

Reply to this Message

CLR - 13 Jan 2006 18:46 GMT

Your sample data, when entered as TEXT, sorts as follows........
001
111
2
22-33
3

Vaya con Dios,
Chuck, CABGx3

> Thanks for the feedback.
>
> Formatting the column as text still gives me the same sort as with a
> numeric search. ie: shorter numbers are first.
>
> Andy Davidson

Reply to this Message

David McRitchie - 13 Jan 2006 22:55 GMT

For this and your previous answer, that is true if you format as text before entry.

But formatting after entry does not change a number to text, nor text to
a number so changing the format would have no effect until reentered.
Reentering would remove leading zeros at least in Excel 2002.

See my previous reply for how to deal with the situation described.
There is also a link to a macro to fix numbers as formatted to text..
http://www.mvps.org/dmcritchie/excel/sorting.htm#mixed .

> Your sample data, when entered as TEXT, sorts as follows........
> 001
[quoted text clipped - 12 lines]
> >
> > Andy Davidson

Reply to this Message

CLR - 13 Jan 2006 23:10 GMT

Thanks David.......I thought I detected some "weirdness" there, (sometimes
it worked, sometimes not), but thought it was just me......now I see
why.....

Vaya con Dios,
Chuck, CABGx3

> For this and your previous answer, that is true if you format as text before entry.
>
[quoted text clipped - 29 lines]
> > > kaduna's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30422
> > > View this thread: http://www.excelforum.com/showthread.php?threadid=500921

Reply to this Message

David McRitchie - 13 Jan 2006 17:34 GMT

See bottom of topic:
Sorting mixed alpha and numeric cells (#mixed)
http://www.mvps.org/dmcritchie/excel/sorting.htm#mixed

I have no idea what this statement means:

> I have lists that were sorted this way but trace how.

Since you have posted from a site whose existence interfers with
Google (web) searches, detracts from Google Groups
searches, and whose primary purpose is to generate
advertising revenues, rather than providing help, I will not
post the code here directly.

It is rather unreasonable to expect people to search Google
first when sites that are intent on advertising revenues simply
put newsgroup postings on a website. If you want to search
newsgroup postings one should use Google Groups search.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I have multiple lists of part numbers, both numeric and alpha numeric in
> varying digit numbers and wish to sort them.
[quoted text clipped - 5 lines]
>
> Rgds

Reply to this Message