Excel 2007 : conditional formatting: cell contains multiple text strings

**acp** · 07-02-2012, 12:10 PM

I'm pretty sure my question has been answered here before, but I don't know or remember what to call what I'm trying to do so I'm having trouble searching for it. In my FoxPro-based database it's called "multiple contains".

In a nutshell: I'm trying to create a formula that will highlight cells based on text they contain (not equal). The challenge is that I have many text strings I'm looking for. I know how to do this one at a time, but I was hoping there'd be an easier way.

Specifically: I want to highlight my entire spreadsheet and set up a conditional format that will highlight any cell that contains "Bob" or "Mary" or "Jim" or "John". The cells equal text like this: "Mary Smith" or "Ms. Mary Smith" or "Jim Bob Jones" or "Mr. and Mrs. Jim Bob Jones".

Jeez, as I'm explaining it, it sounds so familiar, I might have been the one who asked it (so embarrassing if that's true), but it's not in my list of posts under my profile, so maybe it became too complicated to explain and I gave up before posting.

Thanks for any help anyone can offer,

Angie

**dilipandey** · 07-02-2012, 02:06 PM

Hi Angie,

You can do this in formula option under conditional formatting.. e.g. :_

=OR(G10="Mary Smith",G10= "Ms. Mary Smith", G10="Jim Bob Jones", g10="Mr. and Mrs. Jim Bob Jones" )

See attached:-
CF - multiple strings.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**acp** · 07-02-2012, 02:38 PM

Thanks for such a quick response and for the sample spreadsheet, but I don't think I explained myself well. I know how to highlight cells if they're exactly equal to what I'm looking for, but in this situation, I'm looking for cells that contain some of the text that's in a cell. For example, let's say I'm looking for everyone whose name is Mary. If a cell contained "Ms. Mary Smith" or "Mary Jones" or "Mrs. Mary Ann Smith", all three would be highlighted.

As I mentioned, the challenge is that I want to find multiple names at one time. I don't HAVE to do it this way, it just would be way easier, especially since this seems to keep coming up.

Thanks, again, though!

**dilipandey** · 07-03-2012, 01:54 PM

Okay.. use the below formula in condtional formatting logic where I have upgraded it to handle bob and mary as of now :-

=OR(ISNUMBER(MATCH("mary",G10)),ISNUMBER(MATCH("bob",G10)))

Regards,
DILIPandey

<click on below 'star' if this helps>

**galav** · 06-12-2014, 03:38 AM

Hello Sir.... I want a help from you regarding excel...sir I want to highlight cells of a single column with a color which contains two text strings of my desire.

threre are a lot of rows in that column..

as an example I want highlight those cells which contains "concrete" and "slab" from my selected column.

please reply.