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

 

[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