Format>Cells>Special>Zip Code

[MichaelRobert]

Every day I receive a list of Zip Codes covering Sales Leads. The as-received
format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with the
leading zeros omitted, but I need to change it to 5 digit to process further.

Routinely, I use the command "Format>Cells>Special>Zip Code" to add the
leading zeros back. But the command seems unable to convert a '5+4' format to
a simple 5 digit format, and I end up having to delete the hypen and the last
4 digits manually. Am I missing a trick?

Mike

 

[David Biddulph]

Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect.

 

[MichaelRobert]

David:

Thanks for the suggestion but I don't think that 'stored as text' is the
problem. The Format>Cells>Special>Zip Code command has no trouble adding
dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I
have formatted to 'number'.

Is there anyhting else?
M

 

[David Biddulph]

What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still
suspect that you've got text rather than numbers.

If you do have text, then =LEFT(A2,5) ought to work to convert them to 5
ZIPs.
If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on
a quick test it looks as if that would probably work with the text ones
too).

 

[MichaelRobert]

Thanks, David.

ISTEXT returns FALSE; ISNUMBER returns TRUE.

The LEFT command works well - thank you.

Btw, even if I start with a fresh worksheet, and enter numbers to create a
5-4 ZIP (like 32034-1234) the Format>Cells>Special>Zip Code command does not
convert the entry to 5 digits. Should it?

Mike

 

[Beege]

ISTEXT is going return TRUE to the 5-4 zip code. If you enter
00000-1111, Excel assumes its a text entry, otherwise there'd be
confusion on whether it was a zip or a subtraction formula. Strings like
12345 are assumed to be numbers, unless you tell it otherwise, and you
can format numbers to have leading and trailing zeroes, but you can't
format text like that.

My wish is that people would treat zip codes, telephone numbers, serial
numbers (e.g part numbers) as they would text, because nobody is going
to do any calculations to those, except as a text character string.

Maybe clearer, maybe not. Hope it is.

Beege

 

[MichaelRobert]

David and Beege:

What I am taking from this discussion is that the Format>Cells>Special>Zip
Code (5 digits) command will somehow change the format of 5 digits from ?? to
a 5 digit Zip Code (ie no change at all except to add any missing digits as
leading zeroes) . While the 5+4 command will change a 9 digit string into a
5-4 string.

Is that right? If so, it hardly seems worth having a special command.

Mike