#1   Report Post  
Posted to microsoft.public.excel.misc
Karen
 
Posts: n/a
Default Leading Zeros

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Leading Zeros

Try this:
Select the impacted cells, then...
FormatCellsNumber 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" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Karen
 
Posts: n/a
Default Leading Zeros

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

"Ron Coderre" wrote:

Try this:
Select the impacted cells, then...
FormatCellsNumber 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" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Leading Zeros

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) :Doesn't work: returns zero
B1: =SUM(--A1) :Works: returns 225

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

XL2002, WinXP-Pro


"Karen" wrote:

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

"Ron Coderre" wrote:

Try this:
Select the impacted cells, then...
FormatCellsNumber 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" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Spliting a number with leading zeros haitch2 Excel Discussion (Misc queries) 7 September 30th 05 12:09 AM
CSV File - Leading Zeros Floridagal Excel Discussion (Misc queries) 2 August 6th 05 12:34 AM
Format a cell to keep leading zeros. Shadyhosta New Users to Excel 5 July 27th 05 04:37 PM
How do I force leading zeros in an Excel cell? EricKei Excel Discussion (Misc queries) 2 June 15th 05 08:28 PM
Leading zeros Paul Excel Discussion (Misc queries) 4 June 12th 05 04:04 AM


All times are GMT +1. The time now is 05:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"