Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I correct a custom format?
I've created a custom format to display a number as 1111 2222 3333 4444.
However when I enter in the 16 digits, the 16th turns to 0. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I correct a custom format?
I think that Excel only allows 15 digits, so the 16th you are entering will
be always 0, if you are using a number format. One solution is format the cell as text, and then use a formula to add the spaces, something like: =LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4) It is not as neat as a custom format, but it may do the trick. Hope this helps, Miguel. "Brent" wrote: I've created a custom format to display a number as 1111 2222 3333 4444. However when I enter in the 16 digits, the 16th turns to 0. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I correct a custom format?
Thank Miguel,
The formula you gave me worked, however the 16th digit shows up again as a zero. Does anyone know any other way around this? "Miguel Zapico" wrote: I think that Excel only allows 15 digits, so the 16th you are entering will be always 0, if you are using a number format. One solution is format the cell as text, and then use a formula to add the spaces, something like: =LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4) It is not as neat as a custom format, but it may do the trick. Hope this helps, Miguel. "Brent" wrote: I've created a custom format to display a number as 1111 2222 3333 4444. However when I enter in the 16 digits, the 16th turns to 0. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I correct a custom format?
Brent,
Enter the number into cell A1 with a leading single quote mark, to make it a string. HTH, Bernie MS Excel MVP "Brent" wrote in message ... Thank Miguel, The formula you gave me worked, however the 16th digit shows up again as a zero. Does anyone know any other way around this? "Miguel Zapico" wrote: I think that Excel only allows 15 digits, so the 16th you are entering will be always 0, if you are using a number format. One solution is format the cell as text, and then use a formula to add the spaces, something like: =LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4) It is not as neat as a custom format, but it may do the trick. Hope this helps, Miguel. "Brent" wrote: I've created a custom format to display a number as 1111 2222 3333 4444. However when I enter in the 16 digits, the 16th turns to 0. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My Excel 2000 changes the format of my numbers, how do I correct? | Excel Discussion (Misc queries) | |||
hiding % characted in the Y axis using custom format | Charts and Charting in Excel | |||
Custom Format for User Defined Function | Excel Discussion (Misc queries) | |||
adding custom format | Excel Discussion (Misc queries) | |||
Custom Cell Format Will Not Save Correctly | Excel Worksheet Functions |