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.

Finky wrote:
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.


=IF(ISERROR(FIND("-",A14)), no dash found , dash found )

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.

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.

How about showing us *several* representative samples of your data and tell
us what results you expect.

--
Biff
Microsoft Excel MVP


"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.

"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.

You can force the zeroes with a text function if your cell contains a
number, or use the LEN function and pad out with zeroes (or pad out with
zeroes anyway & use the RIGHT function) if you have text.
--
David Biddulph

"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.

On Thu, 13 Aug 2009 13:31:02 -0700, Finky
wrote:

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.

It looks like you are trying to obtain the first five digits of zip codes,
entered in various formats. Try this which should retain leading zeros:

=LEFT(TEXT(SUBSTITUTE(A1,"-",""),"[<100000]00000;00000-0000"),5)

Of course, this does not test to ensure your data is in one of the three
acceptable formats.

--ron

The problem with this is many zip codes begin with zeros, and, with a
varying
amount of digits I couldn't force the zeros

So what does a zip code like 01234 look like in your worksheet, this 1234? I
agree with JoeU2004's comments about making your data all text to begin
with. However, for what I think you are describing, this formula should work
with your current setup...

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

If you ever switch your data to all text so that the leading zeroes will be
displayed, then you can use the simpler (and more efficient) formula I
posted originally.

--
Rick (MVP - Excel)


"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.

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.