How do I find a dash in a cell with numbers in it in excel.

[Finky]

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.

 

[Glenn]

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 )

 

[Rick Rothstein]

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))

 

[Finky]

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.

 

[T. Valko]

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

 

[JoeU2004]

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

 

[David Biddulph]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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.

 

[JoeU2004]

Errata....

=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 -----