sort numbers leading zeros
I am having trouble sorting a column of id numbers because some of them
contain leading zeroes. Excel doesn't recognize that no matter how I format the cells. Help! LS |
Are all your id numbers numeric (no alpha characters in them at all)?
If you put: =counta(A2:Axxx) in an empty cell and =count(a2:axxx) (adjust the range to match your data) Do you get the same answer? If no, then some of the values are stored as text and some(?) are stored as numbers. You can convert them all to numbers by: selecting an empty cell copy it select your range of id's edit|Paste special|Add Format the cells the way you want: format|cells|number tab|custom category 0000000 (to show leading 0's.) Then your data should sort ok. ============ On the other hand, if your id's can look like: 12345 A1234 1234B More work would be required. l smith wrote: I am having trouble sorting a column of id numbers because some of them contain leading zeroes. Excel doesn't recognize that no matter how I format the cells. Help! LS -- Dave Peterson |
Hi LS,
As Dave said, it depends on whether your ids carry characters. If they are all numbers, then another alternative is to create a new column using Value function. For example, if column A contains the original ids: A1 = 00085, in the new column B, B1, set the function =value(A1) will return 85 as a number. Then sort column B instead of column A. Anki "l smith" wrote: I am having trouble sorting a column of id numbers because some of them contain leading zeroes. Excel doesn't recognize that no matter how I format the cells. Help! LS |
All times are GMT +1. The time now is 03:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com