# Conditional format based on count of spaces in cell

For Google Sheets, there's plenty on how to format based on length, and there's plenty on how to count spaces, but I'm struggling to combine the formula.

I understand the usage of ranges for formatting on length:

=len(A:A)>30


And for counting spaces:

=len(A1)-len(SUBSTITUTE(A1," ","")


But how do I apply ranges to the above so that it can be used in conditional formatting?

My goal is to format a cell red if there are more than N spaces in it.

Try using the formula:

=len($A1)-len(SUBSTITUTE($A1," ",""))>N


and the Range of the Conditional Format as A:A

• Thanks BJQ. I used this but without the absolute modifier ($) and it works. This isn't necessary, is it? – JoshM Aug 16 '18 at 8:34 • Cheers! In this case ($) isn't necessary to work with. – BJQ Aug 18 '18 at 5:09

you can try also:

=COUNTIF(LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")), ">30")


note that you don't need to have the ranges in your formula if you work with the Conditional formatting.
all you need to do is to insert formula which will reflect starting point eg. if you want to have conditional formatting from row 6 downwards formula will be:

=COUNTIF(LEN(A6) - LEN(SUBSTITUTE(A6, " ", "")), ">30")


and you set a range to A6:A directly in Conditional formatting:

• Thanks for your answer, user0. Unfortunately pasting your formula didn't work for me but I really appreciate the explanation of how formula is applied in conditional formatting. – JoshM Aug 16 '18 at 8:23
• @JoshM I updated my answer, try now. – user0 Aug 16 '18 at 20:32