Update to formula

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","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","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

"Jen_T" wrote in message
...
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","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot
find")

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

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
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","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot
find")

"Jen_T" wrote:
=IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU",
"0408SL","0408","0109","0209","0309","0409"},A 1)),
"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 -----

"Jen_T" wrote:
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","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find")

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

--
Biff
Microsoft Excel MVP


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

Regards,
Fred

"Jen_T" wrote in message
...
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","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot
find")