Re: Format>Cells>Special>Zip Code

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






.