In Google Spreadsheet, is there a way to conditionally format a cell if it has more than 30 characters?
Not on the cell itself, without the usage of Google Apps Script. Adding a helper column will allow you get realy close:
Now add the following formula in
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
If your cell is A1, in New Google Sheets, in Custom formula is please try:
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:
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:
Hope that helps!
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
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!