Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel97 problem with sort
- Thread starter BarryG
- Start date
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= </a><br>
- Thread starter
- #3
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 174<br>D 200<br>D 082<br>B 200<br>B 190<br>D 190<br><br>If I sort this by column A asc, column B asc, it should result in:<br><br>B 190<br>B 200<br>D 082<br>D 174<br>D 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.
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= </a><br>
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
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). 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= Adams Online</a><br>I'm a professional writer, published internationally.
- Status
