Return blank cell if 'find' statement not true

G

Guest

I wish to search text in A1 for the word "div". If it is present I want
cell A6 to equal A4. If absent, I want cell A6 to be blank.

When '=IF(FIND("div",A1),A4,"")' is placed in cell A6 it gives '#Value'
instead of blank if A1 does not contain"div".

This seems trivial, but how do I remedy the situation?

Thanks
 
B

Biff

Hi!

Try one of these:

=IF(ISNUMBER(FIND("div",A1)),A4,"")

=IF(ISERROR(FIND("div",A1)),"",A4)

Note: FIND is case sensitive so if the cell does not contain the EXACT
(sub)string: div, the formula will return "".

Replace FIND with SEARCH if you do not want the formula to be case sensitve.

Biff
 
G

Guest

Thanks Biff

Biff said:
Hi!

Try one of these:

=IF(ISNUMBER(FIND("div",A1)),A4,"")

=IF(ISERROR(FIND("div",A1)),"",A4)

Note: FIND is case sensitive so if the cell does not contain the EXACT
(sub)string: div, the formula will return "".

Replace FIND with SEARCH if you do not want the formula to be case sensitve.

Biff
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF - AND - OR statement? 3
Average formula where blank cells are counted as zeros 4
COUNT function 2
Return next non-Blank Cell in a Column ? 4
Ignore blanks in multiplication formula 4
Maximum Number of Blank Cells between Non Blank Cells in a Range 5
Having trouble with AVG Function 2
return blank cell if condition met 2

Top