Conditional formatting for multiple partial matches

[gli]

Hello all,

I know I can use conditional formatting for each partial text string I want to effect by using SEARCH, but that would be a lot of single rules and time consuming to implement. I'm looking for a formula that will find any instance of a list of several partial text strings and apply the same format to them.

I have a column about 3000 rows deep with a series of text strings. The text strings start with letters and end with numbers (ex: YY5725 or B7890). The numbers are not important for my needs here, just the letters. These letters will repeat based on the type of record involved. I am looking for a conditional formatting formula that will for example find every instance of B, XX, YY, and ZZ and turn those cells the color I want to use. The formula needs to be able to ignore the numbers and just focus on the letters, which I will define.

Any help would be greatly appreciated, but I am hoping for a non-VBA solution.

 

[Peter_SSs]

Here is one way. Enter the relevant partial texts in a vacant column (I've used col D) then use Conditional Formatting (formula) as shown.

Excel Workbook
	A	B	C	D
1	Data			B
2	YY5727			XX
3	JK45			YY
4	AB7895			ZZ
5	RZZ678
6
7	FTR908
8	XXT675
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=COUNT(SEARCH(D$1:D$4,A2))	text	NO

 

[gli]

Thank you, Peter. That worked nicely!

 

[Peter_SSs]

Thank you, Peter. That worked nicely!

Glad to help.