Excel97 problem with sort

[BarryG]

I sometimes experience problems trying to sort columns that contain numbers formatted as text. The sort either doesn't work at all or sorts in several blocks. I've made sure that there are no leading or trailing spaces or special characters. Any ideas?

 

[Dreamboat]

Barry: Can you give me an example? Also, why are you formatting them as text? Perhaps that will explain for me why they don't sort. <p> <br><a href=mailto: dreamboat@nni.com> dreamboat@nni.com</a><br><a href=

http://www.wedomanuals.com>

</a><br>

 

[BarryG]

I use Excel to manipulate downloads from our main system, SAP. Some values have leading zeroes. If I accept the default format, General, leading zeroes are stripped off. Text retains the zeroes.<br><br>An example of the problem:<br><br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;174<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;082<br>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200<br>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;190<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;190<br><br>If I sort this by column A asc, column B asc, it should result in:<br><br>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;190<br>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;082<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;174<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200<br><br>but doesn't. Sometimes it changes the order, but incorrectly and sometimes it doesn't change the order at all.<br><br>Thanks for you help with this.

 

[Dreamboat]

Okay, if you want to keep exactly 3 digits, then format as numbers. Then, format-special and type 000 in the box for the special formatting. This works great when you want check numbers or account numbers or whatever to have leading zeroes. And it might even sort properly for you. Give it a try. <p> <br><a href=mailto: dreamboat@nni.com> dreamboat@nni.com</a><br><a href=

http://www.wedomanuals.com>

</a><br>

 

[BobCrawf]

If you can import the numerical data as numerical and then use custom format as stated by Dreamboat, that would be the best solution.<br><br>However, If you Must import the numbers as text, there is another solution. Actually more than one.<br><br>Assuming your data is in A2:B7, in C2 ad the formula =VALUE(B2) and drag it down to C7. Now sort A2:C7 using Key1 = A and key2 = C. You'll have the sort you want. You can then delete, leave as is, or hide column C.<br><br>Bob Crawford

 

[Garridon]

Just one other thing worth noting--I encountered the same problem with the numbers that started with zeroes (made it hard to verify serial numbers because it lost the first number).&nbsp;&nbsp;If you put a single quotation mark (') in front of the number, it won't drop the zero.<br> <p>Linda Adams<br><a href=mailto:Garridon@aol.com>Garridon@aol.com</a><br><a href=

http://www.geocities.com/hallona_2000>Linda

Adams Online</a><br>I'm a professional writer, published internationally.