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?

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?

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?

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?