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.
chuckster_ehAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NBVCConnect With a Mentor 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
 
NBVCCommented:
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
 
chuckster_ehAuthor Commented:
I said "Sort anything that looks like a number - as a number"
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
chuckster_ehAuthor Commented:
But I have tried it both ways....
0
 
chuckster_ehAuthor 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
 
NBVCCommented:
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
 
chuckster_ehAuthor 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
 
chuckster_ehAuthor 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
 
NBVCCommented:
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
 
chuckster_ehAuthor 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.

All Courses

From novice to tech pro — start learning today.