MrExcel.com - Your One Stop for Excel Tips & Solutions

Sorting Imported Information

Posted by fishyshrink on February 01, 2002 7:53 AM

I receive very large files from time to time and they come with all the columns formatted as text. Our customers' ID numbers range in length from 4 to 6 digits. When I try to sort the database by ID, Excel doesn't see the 4 digit numbers as being smaller than the 6 digit ones. What I end up with is a list something like this:

1234
1234
1234
12341
12341
12341
2234
2234
2234
22344
22344

I hope that's a good enough example. To side step this problem, I've started converting the original ID column into numerical format, inserting a new column with a numerical formula of A1*0.00000001 and setting the decimal point to 8. This gives me a better list but it's a pain to have to go through all of this just to get a decent sort. I pay commissions based on number of customers per ID so it's very important that I have the list sorted correctly. It would be helpful if I can somehow format one column and not have to go through all these steps. Can someone help me with this situation?

Posted by Russell Hauf on February 01, 2002 8:30 AM

Try this:

Type 0 in a cell. Copy that cell, then select the cells with the values you want to sort. Bring up Paste Special (right-click or Edit-Paste Special) and under 'Operation', select Add. Click OK. You should now be able to sort correctly.

Hope this helps,

Russell

1 1 1 4 4

Posted by Russell Hauf on February 01, 2002 8:31 AM

You can also copy a blank cell instead of 0 (nt)

1 1 1 4 4 : I hope that's a good enough example. To side step this problem, I've started converting the original ID column into numerical format, inserting a new column with a numerical formula of A1*0.00000001 and setting the decimal point to 8. This gives me a better list but it's a pain to have to go through all of this just to get a decent sort. I pay commissions based on number of customers per ID so it's very important that I have the list sorted correctly. It would be helpful if I can somehow format one column and not have to go through all these steps. Can someone help me with this situation?