How can I write a formula to conditional format cells that contain two ampersands anywhere within the cell?

Question

I have a long list of names, most of which contain an ampersand (&). Some contain two ampersands at various places within the cell. I need to conditionally format the cells (just highlight them) in order to find them. I haven't been able to find a formula that can identify and condition a cell that contains two ampersands.

Answer 1

You can use this formula:

=SUM(INT(MID(A1,SEQUENCE(LEN(A1)),1)="&"))=2

SEQUENCE(LEN(A1)) creates an array of integers which is the same length as the number of characters in the text string in cell A1.

We pass that array into the second parameter of MID, which is equivalent to calling MID with each of the numbers 1 through LEN(A1), with the third parameter of MID being 1, each return value is just one character from the text string. So this creates an array of the characters in the text string.

You then compare that array to the value you're searching for, in this case &. This creates an array of TRUE/FALSE where the value is TRUE if the character in that position in the array is an ampersand.

We use INT to convert the TRUE/FALSE to 1/0. You can use -- instead of INT but I think INT is more intuitive. You now have an array of LEN(A1) values which are either 1 if the character in that position is an ampersand, or 0 if it isn't. Wrapping that array in SUM reduces it down to a count of the ampersands in the string, and we simply compare this count to the value you want to check for. If they are equal, the condition is met and the formatting is applied.

You might consider changing the =2 to >2 to account for those cases where there are more than 2 ampersands.