conditional formatting find text within string


New Member
Apr 8, 2013
I have a list of text strings (names). I need to highlight (conditional formatting) any cell that contains any of the names within the text of the cell. I have 250 names. It obviously is taking too long to conditionally format for each name separately. How do I write a formula in conditional formatting that will search all 250 names for me in 50,000 cells.

list of names to search with

cells with text to be searched
test for text ABCD1text
Test for text without any of my names

I have looked at several threads, but can't quite get it to work. I have been going to conditional formatting and trying various formulas. Please help.
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi and welcome to MrExcel.

This probably isn't the best solution, but it appears to work.
If you create a Named Range of your 250 names, then you can include that within a formula, eg...

Excel Workbook
10Red 987*TRUE***

The formulas in C2 is just to check that the formula works.
Within this formula is a Named Range Criteria that holds all the colours in column E.
This formula can then be used in Conditional Formatting with a slight addition.....


To create a Named Range...
Highlight your list of names
Right Click,
Click Name a Range,
Type Criteria into the box.
Click Ok

To apply Conditional Formatting...

Highlight your range of text from top to bottom by clicking the first cell and then pressing ctrl shift down arrow.
Press alt H L N
Click Use a formula etc and paste the formula you have created (or the one above) into the white box.
Click Format and select the formatting you require, click Ok, Ok.

I hope this works for you.

Upvote 0
Thanks, it worked, but I don't understand the SEARCH function and there was a bit of a bug. My search column is EP and my sheet has one row of headers as example above, so I replaced A2 with EP2. My Criteria list is on another sheet with 2 rows of headers. When I did the conditional formatting, one cell above the correct cell was formatted in each case. EP3 gave me two cells above the correct one, EP1 worked and correctly marked the cells of interest. Why do we only have to put in one cell address in the SEARCH function when I am searching a whole array? Why does the cell I select affect the row number that is highlighted? Is it because my Criteria list has 2 header rows instead of 1?
Upvote 0

Can you provide the cell references for your criteria and for the "Search" column.

The Search function is looking at ALL your criteria and seeing if there is a match in each cell, one at a time.

If your first cell is EP2 and you highlight your range from top to bottom, the formula "tells" Conditional Formatting that EP2 is not an Absoulte reference, but is a Relative cell reference, eg Excel would apply it as the following...
and so on

If however, you have Highlighted your range from EP1 and applied this formula...
it tells Excel to highlight EP1 if EP2 meets the criteria, then it will look at cell EP2 with this formula
it tells Excel to highlight EP2 if EP3 meets the criteria, and so on.

I hope that explains it for you, but please provide your cell references and I will try to replicate your problem.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back