Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spliting a number with leading zeros | Excel Discussion (Misc queries) | |||
CSV File - Leading Zeros | Excel Discussion (Misc queries) | |||
Format a cell to keep leading zeros. | New Users to Excel | |||
How do I force leading zeros in an Excel cell? | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) |