Is there such a thing as a contains formula

  • Thread starter Thread starter Hawksby
  • Start date Start date
H

Hawksby

Afternoon all,

I'm looking to reference a cell and return a specific value if it contains a
certain text string / strings. Does anyone know of a way of doing this. The
Text string is not always at the begining or end of the field so left and
right are unusable

Thanks in advance
 
Something like this:

=IF(ISNUMBER(SEARCH("MyWord",A1)),"Text found","Text not found")

or this, if you need case-sensitive
=IF(ISNUMBER(FIND("MyWord",A1)),"Text found","Text not found")

Note, if looking for multiple strings, use the AND function.
 
Brilliant thank you Luke, you don't know how i can do this with 11 searches
do you? Obviously nested if statements don't work after 7. I should have
mentioned that 1st i think
 
Presuming the cell you are checking is A2, and B2:B10 contain a list of words
that you want to search for:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B2:B10,A2)))),"Text found","Text not found")
 
.. how i can do this with 11 searches

Assume the 11 text/items to be searched is housed in E2:E12
Assume the data is running in A2 down
In B2:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A2,$E$2:$E$12)))>0,"Yes","No")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
Sorry guys i think i'm confusing eveyone here.

Ok i have a list of data in Cells A1:A1000. In Cells B1:11 i have a list of
words. What i need to do is say if A1 contains B1 then return Monday if A1
contains B2 then Tuesday.......all the way to A1 contains B11 then return
Sunday

Sorry for the confusion earlier
 
Hmm. While I'm not sure how you have 11 cells containing Monday through
Sunday (7 days in week?), here's an modified setup. Using your setup, and
expanding to ahving the values you want (days of week?) in range C1:C11

This array** formula:

=INDEX($C$1:$C$11,MAX(IF(ISNUMBER(SEARCH($B$1:$B$11,A1)),ROW($C$1:$C$11))))

**Confirm formula by holding down Ctrl+Shift, and then pressing Enter.
 
Back
Top