Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
Displaying leading zeros in an Excel spreadsheet | Excel Discussion (Misc queries) |