Excel conditional formatting - multiple "begins with" statements

1
votes

I have workbooks that have cells with lots of conditional formatting applied. The problem is that the workbooks must be saved in in 1997-2003 Excel format, .xls instead of the modern .xlsx format. If they're saved with the .xls extension, only 5 conditional formats can be saved per cell.

Because of that, I can't use the built-in "Cell Value begins with" conditional formats; I'm going to need to use formulas instead.

How would I construct a formula to format cells that began with multiple characters? For example, I need a formula to check to see if cell K2 begins with A, B, M, or D. I tried using this formula, but it didn't work:

 =OR(SEARCH("A",$K2)=1,SEARCH("B",$K2)=1,SEARCH("M",$K2)=1,SEARCH("D",$K2)=1)
2
excelconditional-formatting

2 Answers

2
votes

This formula worked for me. It only formats the cell if it begins with the letters you specified. I wasn't sure if you wanted different formatting for each letter.

=OR(LEFT($K$2,1)="A",LEFT($K$2,1)="B",LEFT($K$2,1)="M",LEFT($K$2,1)="D")
1
votes

try: =IF(OR(LEFT(K2,1)="A",LEFT(K2,1)="B",LEFT(K2,1)="M",LEFT(K2,1)="D"),1,0)

...you could conditionally format based on 1 or 0.