Problem adding one more IF statement to formula

[Ann Scharpf]

I have a formula which copies full or partial cell
contents based on the cell contents. (Thanks to Harlan
for helping with this.)

=IF(EXACT(LEFT(Desc,2),"ck"),IF(FIND("-TO-",Desc&"-TO-")
<LEN(Desc),LEFT(Desc,FIND("-TO-",Desc)-1),Desc),"")

I have now discovered that there are rows with the "ck" in
positions 1&2 of Desc ... that also have #MERLE in them.
I don't want to include these values. I have tried to add
another IF statment to the formula:

=IF(FIND("MERLE",Desc),"",IF(ISERR(FIND
("MERLE",Desc)),"",IF(EXACT(LEFT(Desc,2),"ck"),IF(FIND("-
TO-",Desc&"-TO-")<LEN(Desc),LEFT(Desc,FIND("-TO-",Desc)-
1),Desc),"")))

This works gloriously well, AS LONG AS there is a MERLE in
the Desc cell. If not, I get a #VALUE error. I have been
mucking around trying to get the syntax right so that I
will just get "" if I error out on finding MERLE. Is this
possible to do?

Thanks for you help.

Ann Scharpf

 

[Frank Kabel]

Hi Ann
try

=IF(ISNUMBER(FIND("MERLE",Desc)),"",IF(ISERR(FIND
("MERLE",Desc)),"",IF(EXACT(LEFT(Desc,2),"ck"),IF(FIND("-
TO-",Desc&"-TO-")<LEN(Desc),LEFT(Desc,FIND("-TO-",Desc)-
1),Desc),"")))

 

[Ann Scharpf]

-----Original Message-----
Hi Ann
try

=IF(ISNUMBER(FIND("MERLE",Desc)),"",IF(ISERR(FIND
("MERLE",Desc)),"",IF(EXACT(LEFT(Desc,2),"ck"),IF(FIND("-
TO-",Desc&"-TO-")<LEN(Desc),LEFT(Desc,FIND("-TO-",Desc)-
1),Desc),"")))

Frank,

I tried that and it eliminates the #VALUE error ... but it
also eliminates the copying of the valid cell text that
starts with "ck". ALL my cells end up blank.

Ann

 

[Frank Kabel]

Hi ann
sorry I just looked at your first statement. Try

=IF(ISNUMBER(FIND("MERLE",Desc)),"",IF(EXACT(LEFT(Desc,2),"ck"),IF(FIND
("-TO-",Desc&"-TO-")<LEN(Desc),LEFT(Desc,FIND("-TO-",Desc)-1),Desc),"")
)

 

[Guest]

-----Original Message-----
Hi ann
sorry I just looked at your first statement. Try

=IF(ISNUMBER(FIND("MERLE",Desc)),"",IF(EXACT(LEFT (Desc,2),"ck"),IF(FIND
",Desc)-1),Desc),"")
)

Thanks, Frank! That worked beautifully. So, this worked
ok because ISNUMBER does not yield a #VALUE error when it
doesn't find a match?

Ann

 

[Frank Kabel]

Hi Ann
this is correct. In your previous formula you evaluated just the return
value of FIND. So ISNUMBER checks if an error occurs and if not create
a blank cell