Update to formula

  • Thread starter Thread starter Jen_T
  • Start date Start date
J

Jen_T

Is it possible for a formula to look in cell and check if it contains
specific text? I was provided the following formula, below, but it only
checks to see if cell value contains only those values but not if it includes
it.

For example: cell value - Test0408AU ="Exist"

=IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408SL","0408","0109","0209","0309","0409"},A1)),"Exist","Cannot find")
 
Search accepts wildcards, so if you want to search for contains "Q3", use
"*Q3*". Ditto for the others in the list.

Regards,
Fred
 
That formula works for me. I would note, though, that you do not need 0408AU
in the list... any time 0408 is in the list, a match will be found for that
number which appears later on in your list... that is, the 0408 text can be
surrounded by any characters and the formula will report "Exist".
 
Jen_T said:
=IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU",
"0408SL","0408","0109","0209","0309","0409"},A1)),
"Exist","Cannot find") [....]
it only checks to see if cell value contains only those
values but not if it includes it.
Click to expand...

That is incorrect. It works just fine with your test data, Test0408AU.

Are you copy-and-pasting the formula from the Formula Bar?

I wonder if you have some subtle error in the actual formula, which you
unconsciously corrected if you retyped the formula in your inquiry.

Note that "0408AU" and "0408SL" are redundant, since you have "0408". But
that should not be causing any problems.


----- original message -----
 
In that application no wildcards are needed.

You would use wildcards if:

Search for Q?3

Where ? is any single character

Search for Q*3

Where * is any number of characters or no characters followed by a 3.

A2 = Q1123

=COUNT(SEARCH("Q?3",A2)) = 0
=COUNT(SEARCH("Q*3",A2)) = 1

A2 = Q13

=COUNT(SEARCH("Q?3",A2)) = 1
=COUNT(SEARCH("Q*3",A2)) = 1
 
Back
Top