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:


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.

2 Answers 2


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, 2018 at 8:34
  • Cheers! In this case ($) isn't necessary to work with.
    – BJQ
    Aug 18, 2018 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:

enter image description here

  • 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, 2018 at 8:23
  • @JoshM I updated my answer, try now.
    – user0
    Aug 16, 2018 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.