conditional formatting find text within string

[jhunter]

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
ABCD1
AF4
AGTPBP1
etc.

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.

 

[Akashwani]

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
	A	B	C	D	E	F
1	Data	*	Check	*	Criteria	*
2	Red123	*	TRUE	*	Red	*
3	123Blue	*	TRUE	*	Blue	*
4	Yello1234	*	FALSE	*	Yellow	*
5	Black987	*	TRUE	*	Black	*
6	Pinky2222	*	TRUE	*	Pink	*
7	Orang9876	*	FALSE	*	Orange	*
8	Green99	*	FALSE	*	*	*
9	Grey9876	*	FALSE	*	*	*
10	Red 987	*	TRUE	*	*	*
11	*	*	*	*	*	*
Sheet15

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

=SUMPRODUCT(--(ISNUMBER(SEARCH(Criteria,A2))))>0

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.

Ak

 

[jhunter]

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?

 

[Akashwani]

Hi,

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...
EP2 =SUMPRODUCT(--(ISNUMBER(SEARCH(Criteria,EP2))))>0
EP3 =SUMPRODUCT(--(ISNUMBER(SEARCH(Criteria,EP3))))>0
and so on

If however, you have Highlighted your range from EP1 and applied this formula...
=SUMPRODUCT(--(ISNUMBER(SEARCH(Criteria,EP2))))>0
it tells Excel to highlight EP1 if EP2 meets the criteria, then it will look at cell EP2 with this formula
=SUMPRODUCT(--(ISNUMBER(SEARCH(Criteria,EP3))))>0
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.

Ak