Re: Format>Cells>Special>Zip Code by MichaelRobert
MichaelRobert
Wed May 14 15:46:04 CDT 2008
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
"Beege" wrote:
> 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 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
> >
> > "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@discussions.microsoft.com> wrote in message
> >> news:1853AC26-B1F6-4815-B4AF-AE8AFCB1FF7C@microsoft.com...
> >>> 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@discussions.microsoft.com> wrote in
> >>>> message
> >>>> news:61BE07BD-3734-4DDA-9270-F1E15B2416A5@microsoft.com...
> >>>>> 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
> >>>>
> >>>>
> >>
> >>
>