Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.
0
chuckster_eh
Asked:
chuckster_eh
  • 6
  • 4
1 Solution
 
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
 
chuckster_ehAuthor Commented:
But I have tried it both ways....
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
NBVCCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now