Update to formula

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

 

[Fred Smith]

Search accepts wildcards, so if you want to search for contains "Q3", use
"*Q3*". Ditto for the others in the list.

Regards,
Fred

 

[Rick Rothstein]

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

 

[Joe User]

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

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

 

[T. Valko]

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