MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting problem


Posted by David on August 15, 2000 1:49 AM

Is there any reason anyone can think of where sorting wouldn't work

I have a list of close to 600 serial numbers. Most are numbers but some start with letters. When I sort there are always a few numbers at the beginning of the list that just don't belong there. They belong further down on the list. Any clues.
They are formatted as general none are formula's or links. They are selected in the sort. This is how the list starts. After 0000 they are fine.

1212
11022
90600
101328
139006
6828003
50813503
96003606
98000574
98001578
98263040
0000
0000524
0008698


Posted by Thomas Venn on August 16, 0100 9:50 AM

Your problem is occuring because the values where you imported it from thinks the values are TEXT. Because Excel thinks it is text, it will sort starting at zero, then one, two, and so on. the way to solve this is to either import everything as text so that every value has a leading zero. Or, after your data is in Excel, you can go to Data->Text to columns...-> then follow the wizard all the way through, but at step 3, choose "General". This will convert all text into numeric values.

When you say that your format is general, you are probably using the formatting function. This will not work if your data is TEXT, it will work only if your data is numbers.

cheers,

thomas

Posted by David on August 16, 0100 10:25 PM


That was it exactly thomas

thank you very much