IF(FIND Formula gives #VALUE! error

[XR6T4GC]

I have a brick selection sheet with Data Validations in Lists of Brick Blends.
I have added * to the end of each brick blend type to signify the amount of
different bricks ie. Colonial Blend** means 2 x different bricks required
(Non Chargeable to client) and Homestead Blend*** means 3 x Different Bricks
required. (Chargeable to client)

I have tried to write a formula that puts an X in a cell adjacent to the
Data Validation cell (means Chargeable to client) for 3 brick blend and if it
is anything else leave the cell blank. I keep getting the #VALUE! Error.

For Example:- =IF(FIND("***",J19,1)>0,"X","")
It works fine putting the "X" value in the adjacent cell, but when I want it
to return a value of nothing ("") it gives me the error.

 

[Dave F]

One possible fix:
=IF(ISERROR(FIND("***",J19,1)),"",IF(FIND("***",J19,1)>0,"X"))

Dave

 

[Joel]

ther werre two things wrong. first the * ws being treated as a wild carrd
character not a real *. Need to switch to Search to handle the *. the ~
tells search tto look for the * (not a wild carrd). Second problem if ***
wasn't found a value error is returned from both find and search. had to add
ISNUMBER to eliminate the error that occured when *** was not found.

=IF(ISNUMBER(SEARCH("~*~*~*",D8,1)),"X","")

 

[Dave Peterson]

Another one:

=IF(ISNUMBER(FIND("***",J19)),"X","")

 

[Dave Peterson]

I don't think you want the tilde character if you use =Find(). But when you
switched to =search(), it was required.

(I thought it was interesting.)

 

[XR6T4GC]

Thanks Dave, Much appreciated.
I'm just an amateur at this, and guys like you are worth their weight in gold.
The girls at work will be most impressed when I tell them "I" figured it out
LOL.
Thanks again mate.
Grant.

 

[XR6T4GC]

Thanks Joel,
As I said to Dave, your help is much appreciated.

Grant.