Errata....

I wrote:
=if(len(A1)<=5, text(A1,"00000"), left(A1,5))

Oops: I made an inexplicable assumption that is incorrect.

Rick's 2nd formula is the correct one to use. To reiterate:

=TEXT(LEFT(A1, FIND("-", A1&"-")-1), "00000")


----- original message -----

"JoeU2004" wrote in message
...
"Finky" wrote:
The problem with this is many zip codes begin with zeros, and, with a
varying
amount of digits I couldn't force the zeros, but I appreciate your help.

It sounds to me like you are treating some zip codes as numbers (those
without dashes) and some zip codes as text (those with dashes).

First, let me say that I think that is a bad idea in principle. More
about that below.

But if my assumption is correct, I think the following should work in both
cases:

=if(len(A1)<=5, text(A1,"00000"), left(A1,5))

Note that the result is always text.

Since some zip codes __must__ be treated as text (those with dashes),
__all__ zip codes should be treated as text. It is a good idea for cell
values to be homogenous. This makes it easier to manipulate them in other
formulas, as you see here.

(One exception: the null string ("") should be permissible in cells that
normally have numbers. Ideally, Excel would treat the null string as zero
in numeric expression, just as it treats empty cells. But Excel does not
<sigh.)

If you are importing the data, usually you can tell the Import Wizard to
treat the column with zip codes as text.

If you are entering the data manually, prefix the zip code with a single
quote (aka apostrophe). Alternatively, set the cell format to Text before
entering data.


----- original message -----

"Finky" wrote in message
...
The problem with this is many zip codes begin with zeros, and, with a
varying
amount of digits I couldn't force the zeros, but I appreciate your help.

"Rick Rothstein" wrote:

If you only want the first 5 digits and they are always before the dash,
why
not just pull them out directly...

=LEFT(A14,5)

If you want to use this across cells that might be blank...

=IF(A14="","",LEFT(A14,5))

--
Rick (MVP - Excel)


"Finky" wrote in message
...
I'm working in Excel 2003 with zip codes, people supply them in 5
digit, 9
digit or 9 digit with dash. I used
=IF(FIND("-",(A14))0,LEFT(A14,FIND("-",(A14))-1),A14) to get the 5
digit
but
if there is no dash it will value out. Does anyone know how I can test
the
cell for the dash first.