CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaconditional-formatting
randy dickinson
randy dickinson

Reputation: 91

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

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.

Upvotes: 1

Views: 162

Answers (2)

JvdV
JvdV

Reputation: 75850

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

Upvotes: 3

Tom Sharpe
Tom Sharpe

Reputation: 34210

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

enter image description here

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

Upvotes: 2

Related Questions