Search Multiple Text String in Cell

**ktj** · 11-04-2011, 05:03 AM

Hello,

I have been trying for the past several days on how to efficiently do the following:

I would like to search a cell for one of 3 specific texts and if one of these text is in the cell then copy or print this text in the adjacent cell.

For example:
Cell A1 contains the text "apple pear 55 hospital gate bn cn weq"

I need to look for the text "55 hospital" or "cat" or "dog" in A1 and if any one of these text exists, then copy that text into cell B1. If it doesn't then return a blank cell B1.

Therefore in the above example, B1 will return "55 hospital"

Hope someone will be able to help.

Thank you in advance!

**brokenbiscuits** · 11-04-2011, 05:12 AM

=IF(ISERROR(FIND("55 hospital",A1,1)),IF(ISERROR(FIND("cat",A1,1)),IF(ISERROR(FIND("dog",A1,1)),"","dog"),"cat"),"55 hospital")

**ktj** · 11-04-2011, 05:21 AM

brokenbiscuits,

Thank you very much! that work perfectly!!!!,

rgds,
Ken

**ktj** · 11-07-2011, 12:58 AM

Hi,

The above results worked perfectly until I came across other cells where the example became a little complicated....

My example before :"apple pear 55 hospital gate bn cn weq" what if my cells turned out to be the following,
Cell A1 "apple pear 1D hospital gate bn cn weq" and some cells
Cell A2 "apple pear 2D hospital gate bn cn weq"
Cell A3 "apple pear hospital gate bn cn weq"
Cell A4 "apple pear gate weather sea house grass"

How do I now, look for "1D hospital" or "2D hospital" or "hospital" and if the text string exists copy to cell B1 and if neither of the 3 text string exists in the cell then return a blank cell?

Hope you're able to help. Thank you in advance again!!!.

**JBeaucaire** · 11-07-2011, 12:00 PM

If you want to search for 1D Hospital or 2D Hospital or Hospital, then this:

=IF(OR(ISNUMBER(SEARCH({"1D hospital","2D hospital","hospital"}, A1))),A1, "")

...but for that set of strings, just search for "hospital", right?

Anyway, that formula shows you how to search for a series of strings in the same cell. {"cat", "dog", "hospital"}