Stop special format 0000 from being changed to a Zip code

[David20]

I'm trying to set a cell to accept a 4-digit number that may have leading zeros. I've set Numbers - Special Formatting to "0000" but Excel changes it to be a postal zip code from Portugal. Any idea how to keep it from using an existing formatting when I want to use a custom special formatting?

 

[Weazel]

After some playing around I came up with a custom number format that seems to work....
_00000 no quotes or anything. The format also keeps the cell as a number so you should be able to do calculations on it.
It doesn't make sense to me but it seems to work

 

[Rick Rothstein]

After some playing around I came up with a custom number format that seems to work....
_00000 no quotes or anything. The format also keeps the cell as a number so you should be able to do calculations on it.

Interesting, although I probably would not use a 0 after the underbar as that might confuse people into thinking the value should be zero-padded to 5 places, not 4. May use the "standard" left parenthesis instead?

_(0000

EDIT NOTE: Just tried it will a single space in front of the 4 zeroes (instead of the underbar/right parenthesis) and that works also (and looks more "natural" in the Custom Format Type field as well).

 

[Norie]

How about this?

0000;0000;0000

Or even

0000;

 

[Weazel]

Interesting, although I probably would not use a 0 after the underbar as that might confuse people into thinking the value should be zero-padded to 5 places, not 4. May use the "standard" left parenthesis instead?

_(0000

EDIT NOTE: Just tried it will a single space in front of the 4 zeroes (instead of the underbar/right parenthesis) and that works also (and looks more "natural" in the Custom Format Type field as well).

I agree with you on the extra "0", didn't really think about the fact that others might be looking at it, I really like the leading space idea much better

 

[Rick Rothstein]

How about this?

0000;0000;0000

Or even

0000;

But of course!

Although that does not answer the question why are 4 zeroes automatically being assumed to be the Portuguese zip code format.

 

[RonB1111]

I can duplicate this problem on Excel 2007 (all updates installed), but the problem does not occur on Excel 2000 which I have on an older computer that hasn't been updated since 2009.

 

[Rick Rothstein]

I can duplicate this problem on Excel 2007 (all updates installed), but the problem does not occur on Excel 2000 which I have on an older computer that hasn't been updated since 2009.

I am duplicating the problem on XL2003 (SP3). The problem could still be in some "core" Office or Excel DLL that got updated (one that XL2000 and earlier didn't use)... again, if this is not a "new problem" I find it strange that it has not been noticed before.

 

[David20]

How about this?

0000;0000;0000

Or even

0000;

Perfect, Norie!

I'd like to hear if anybody finds a reason for this little anomaly.