# Excel Sorting of Text

I imported a 14000 rows of data into an excel spreadsheet and want to sort by column A which has numbers anywhere from 3 digits like this: 019 and 5 digits like this:  47384.   Some have this too:  0003a.

I had to import this column as text so that excel wouldn't mangle all the data and leave my leading zero's alone (why on earth does it do this).

Anyways - I can't sort it now.  Custom sorts don't work either.  I tried to sort it like this; 000,111,222,333, etc all the way up to 99999.

Help.
###### Who is Participating?

x

Commented:
Ok, how about you add a helper column that gets the length., then sort by that column first,.

so if data is in column E, use formula in F2:  =LEN(E2)  copied down.

Then select both columns, sort first by column F, ascending, then column E, ascending
0

Commented:
When you went to sort, did Excel ask if you want to sort text that looks like numbers as numbers?  Did you reply not to (ie sort text and numbers separately)?
0

Author Commented:
I said "Sort anything that looks like a number - as a number"
0

Author Commented:
But I have tried it both ways....
0

Author Commented:
It seems to be sorting according to the actual number itself and not the digits.  For example its currently sorting like this: 01889,019,01900

But I want it to sort like this:

019,01889,01900

I want the number of digits to be the first order of sorting, then the sequentially.
0

Commented:
If I enter those 3 numbers as text strings and sort, and say sort anything that looks like a number as a number... then it sorts them as you said you desire.
0

Author Commented:
try adding a few more numbers,

019
00020
00047
00053
00059
00070
077

I want it to look like this
019
077
00020
00047
00053
00059
00070
0

Author Commented:
My data is in column A.  I made a column b and put in =LEN(A2).  First row is header data.

It doesn't do anything....just says =LEN(A2) in the cell.   I must be missing something.
0

Commented:
That means column B is formatted as TEXT.  Format the column as General, then go to B2, re-enter the formula and copy it down again.
0

Author Commented:
awesome.  that was great. thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.