Conditional formatting based on number of characters

Question

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

Answer 1

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:

=len(XY)>30

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:

=len(A4)>30

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:

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

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:

=LEN(A1)

Hope that helps!

Answer 2

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

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

Now add the following formula in B1:

=ARRAYFORMULA(LEN(FILTER(A:A;A:A<>""))>30)

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

Answer 3

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 → Format → Conditional Formatting

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

In the range field:

A1:A1

Set the colour as you wish.

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

Answer 4

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.