Leading Zeros

[Karen]

I have some numeric values that have leading zeros. Some have one and some
have more than one. I want the leading zeros to show. I know that I can
type in an apostrophe in front of the numbers and the leading zeros will
display. The only problem is, I want to use this in a calculation. I also
know that you can custom format the cells. But in this case, when I don't
know how many leading zeros I'm going to have, how would I customize the
cells?

Thank you, Karen

 

[Ron Coderre]

Try this:
Select the impacted cells, then...
Format>Cells>Number tab>
Category: Text
Click the [OK] button

That will make Excel treat all inputs as text.
Also, if those inputs only include numeric information (no letters), like:
00123, you can use those values in calculations and they will be treated as
numbers

Example:
A1: 002 (formatted as text)
B1: =A1*5 (returns 10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Karen]

Thank you for your quick response - If the cells are formatted as text, I
cannot use them in a calculation. Actually, typing an apostrophe in front of
the numbers is a shortcut to formatting the cell as text.
Karen

Try this:
Select the impacted cells, then...
Format>Cells>Number tab>
Category: Text
Click the [OK] button

That will make Excel treat all inputs as text.
Also, if those inputs only include numeric information (no letters), like:
00123, you can use those values in calculations and they will be treated as
numbers

Example:
A1: 002 (formatted as text)
B1: =A1*5 (returns 10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have some numeric values that have leading zeros. Some have one and some
have more than one. I want the leading zeros to show. I know that I can
type in an apostrophe in front of the numbers and the leading zeros will
display. The only problem is, I want to use this in a calculation. I also
know that you can custom format the cells. But in this case, when I don't
know how many leading zeros I'm going to have, how would I customize the
cells?

Thank you, Karen

 

[Ron Coderre]

Excel won't automatically treat text-formatted values as numbers in ALL
instances.
You'd need to coerce the type conversion, typically by using a --(dbl neg
operator) or performing a direct mathematical operation (+,-,*,/). Experiment
to see which others work.

Example:
A1: 00225 (formatted as text)
B1: =SUM(A1) oesn't work: returns zero
B1: =SUM(--A1) :Works: returns 225

***********
Regards,
Ron

XL2002, WinXP-Pro

Thank you for your quick response - If the cells are formatted as text, I
cannot use them in a calculation. Actually, typing an apostrophe in front of
the numbers is a shortcut to formatting the cell as text.
Karen

Try this:
Select the impacted cells, then...
Format>Cells>Number tab>
Category: Text
Click the [OK] button

That will make Excel treat all inputs as text.
Also, if those inputs only include numeric information (no letters), like:
00123, you can use those values in calculations and they will be treated as
numbers

Example:
A1: 002 (formatted as text)
B1: =A1*5 (returns 10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have some numeric values that have leading zeros. Some have one and some
have more than one. I want the leading zeros to show. I know that I can
type in an apostrophe in front of the numbers and the leading zeros will
display. The only problem is, I want to use this in a calculation. I also
know that you can custom format the cells. But in this case, when I don't
know how many leading zeros I'm going to have, how would I customize the
cells?

Thank you, Karen