In Google Spreadsheets, is there a way to conditionally format a cell if it has more than 30 characters?


Yes! Highlight the cells you want to format. Go to Format > Conditional Formatting.

Once you're in the conditional formatting dialog, choose "Custom Formula Is" from the select box (it's the last option). In the text field type:


X will be the column you're in and Y will be the row you want it to start at. So if you want to check the entire column A starting at row 4, your formula would look like this:


Then just assign a background color/text color. If you have selected an entire row, you will need to adjust the range if you've got a couple of heading cells. In my case, I selected the entire column but I only wanted to check starting at A4 and continuing to the last row in the column. In this example, the range then should be A4:A.

Here's a screenshot of what I had:

Screenshot of the conditional formatting dialog with instructions on how to conditionally format strings that are longer than X characters

And here's how the data looks after clicking "save rules":

Formatted strings.

I added a second column in there that counts the length of the string. That was just as simple as highlighting the column and adding a function to each cell:


Hope that helps!

  • Don't forget the = at the start of the custom formula. I was trying just "LEN($A1) > 10" and it wasn't working, until I put the equals sign in!
    – Andrew
    Jan 12 '17 at 16:45

Not on the cell itself, without the usage of Google Apps Script. Adding a helper column will allow you get realy close:
enter image description here

A1 and A4 both have more than 30 characters. Set the whole column to have a conditional format like this:
enter image description here

Now add the following formula in B1:


This will check column A, whether the entry has more than 30 characters and will return TRUE or FALSE.

See example file I've prepared: more than 30 characters


To make sure you don't have to do this for each cell separately, you can play with the range function:

To format cell A1 if the length of the text in it is longer than 100 chars:

Google Sheets menu → FormatConditional Formatting

=LEN(A1)>100         //or: =LEN(A1:A1)>100

In the range field:


Set the colour as you wish.

Simply pull the cell down to let this rule apply to other cells in column A!


If your cell is A1, in New Google Sheets, in Custom formula is please try:

=len(A1)>30 withRange: `A1` and the formatting of your choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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