Re: Format>Cells>Special>Zip Code

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 wrote:
Thanks, David.

ISTEXT returns FALSE; ISNUMBER returns TRUE.

The LEFT command works well - thank you.

Btw, even if I start with a fresh work***, 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

"David Biddulph" wrote:

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).
--
David Biddulph

"MichaelRobert" <MichaelRobert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1853AC26-B1F6-4815-B4AF-AE8AFCB1FF7C@xxxxxxxxxxxxxxxx
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" wrote:

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

"MichaelRobert" <MichaelRobert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:61BE07BD-3734-4DDA-9270-F1E15B2416A5@xxxxxxxxxxxxxxxx
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




.

Quantcast