Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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:

=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

share|improve this answer

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.
share|improve this answer

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:

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:

=LEN(A1)

Hope that helps!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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