Conditional format Cells in range based on count of specific symbol in string-CodePudding

Range A2:A2000 has values that should contain only 1 @ symbol. I need conditional formatting for all cells in this range that contain more than one @ symbol.

I have tried this: =SUMPRODUCT(LEN(A2:A2000)-LEN(SUBSTITUTE(A2:A2000,"@","")))>1

but it does not seem to work. Where have I lost my way? I am currently using Google Sheets.

CodePudding user response：

Try to use the following conditional formatting formula:

=REGEXMATCH(A2,"@.*@")

The regular expression "@.*@" Would check if the input holds two '@' characters with 0 characters of any kind inbetween.

Apply to appropriate range A2:A2000

CodePudding user response：

You can use your formula, but do not enclose it in sumproduct. An ordinary formula with relative references (i.e. not using dollar signs) that works for the top left hand corner of the range will work for the rest of the range because CF automatically adjusts the references as if you were dragging the formula down (in this case) or across.

=LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))>1

There is no reason not to use regexmatch instead, except perhaps for compatibility with Excel.