Personalized number format

Occasional Visitor

Why in a personalized number format like this: 

       #### #### #### ####

excel substitutes automatically the last number with a zero (0) ?

I want to format credit card numbers this way, leaving a small space between groups of 4 numbers, it then shoud show this way: 1234 5678 9012 4444

but no matter what I do, it always shows 1234 5678 9012 444substituting with a 0 the last number.

Anyone has a suggestion?

1 Reply
It is a limit in Excel to accept only 15 digits for a number (i.e. it has nothing to do with the custom number format). You can enter 16 digits as TEXT (either format cell as text before you enter the value or start with an apostrophe ' ) but that doesn't help you with your visual formatting. That said you can have another column that can automatically transform that text value and insert spaces:
=LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4)
or in Excel365
=TEXTJOIN(" ",,MID(A1,SEQUENCE(4,,1,4),4))