If NOT FOUND, perform this calculation

[MattGreer]

I'm working in Excel 2007. I want to search through the text in column A and give a result in column B. Here's what's going on:

Col A:
AE-3452
4567

So if I search in Row 2, and the "-" is not found, I want to simply reproduce the contents of column A in column B. However, the formula for FIND gives me the "#VALUE!" whenever it can't find something. I tried:

=IF(FIND("-",A1)="#VALUE!","BAD","OK")

or

=IF(FIND("-",A1)=#VALUE!,"BAD","OK")

but I still get an error.

How can I "test" a cell to determine if there is a character in it, and if the character does not appear perform a certain calculation on it?

Thanks!!!

Thanks!!


Matt

 

[AnotherHiggins]

FIND and SEARCH only return values if the search string is found. Otherwise they return an error. "#VALUE!", with the quotes, is a text string that does not equal the error that is being returned.

Instead, you can test for an error by using IsError or test for a value by using IsNumber.

Examples:
[tab]=If(IsNumber(Find("-", A2)), "OK", "BAD")
or
[tab]=If(IsError(Find("-", A2)), "BAD", "OK")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mscallisto]

try this
=IF(ISERROR(SEARCH("-",A1,1)),"Bad","OK")

 

[mscallisto]

wow that John is FAST!!!!

 

[SkipVought]

My protege. I am so proud of John flyin' fingers!!! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Aw, shucks

I've been doing my finger exercises to try to keep up with you guys.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.