Is there such a thing as a contains formula
 

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

Is there such a thing as a contains formula
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hawksby" wrote:

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

Is there such a thing as a contains formula
 

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

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hawksby" wrote:

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

Is there such a thing as a contains formula
 

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

Luke M
*Remember to click "yes" if this post helped you!*


"Hawksby" wrote:

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

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hawksby" wrote:

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

Is there such a thing as a contains formula
 

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

Is there such a thing as a contains formula
 

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

"Max" wrote:

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

Is there such a thing as a contains formula
 

Try in C1, normal ENTER:
=INDEX($B$1:$B$11,MATCH(TRUE,INDEX(ISNUMBER(SEARCH ($B$1:$B$11,A1)),),0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Hawksby" wrote:
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

Is there such a thing as a contains formula
 

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$1 1,A1)),ROW($C$1:$C$11))))

**Confirm formula by holding down Ctrl+Shift, and then pressing Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hawksby" wrote:

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

"Max" wrote:

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