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


Joel wrote:

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

"XR6T4GC" wrote:

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 Peterson