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