How to highlight a excel cell in red if more than 140 chars in it?

[Autoschieber]

Hi,

I have editors / copyreader that write twitter headlines & meta descriptions in excel cells.

Do you know a way for google docs & excel how to highlight the text of a cell in red if there are more than 140 chars ( or number to define) in the cell

so the editor can see if the tweet / description is too long?

[Manseo]

After an hour or so of messing around with it, I found a Solution!

So you have to have two rules.

First one is Cell is Empty, set the background color to white (not sure why this is needed, but it is crucial for it to work)

Second rule is Custom Formula is =len(B2)>150, set background color to red (this will set it to red if the their is over 150 characters.

This test spreadsheet really helped me out. (gotta make a copy to mess with it)
https://docs.google.com/spreadsheets/d/1ScDqtZvhsmf2iByAuxHBu0-ESpX_TE5pdtBvAxOJ99E/edit#gid=1620525099

Your welcome,
Chris

U7rW8SV

[mikecartmel]

For gDocs:

Create extra column and add formula =len(cell ref containing text)

Add conditional formatting to the resulting number where greater than 140

[Webmaster_SEO]

Here is how you get the highlighting done where character is >140

1. Goto new rules in Conditional formatting dropdown

2. Select "use a formula to determine which cell to format"

3. In the edit section under "format value where formula is true" give following:

=len(A1)>140

and then select custom formatting (fill color)

Now simply use format painter option to fill the complete column (all individual cells) with this formatting

*Note I am using Excel 2010

[Cornel_Ilea]

Hi Axel,

As far as I see you can't measure the string length in 'Conditional Formatting' option in gDocs.

This is how it should work in Excel gDocs

Lets say that your table looks like this:

1. Column A  = No

2. Column B = Strings

This is what you should do.

1. Create a separate empty column C

2. Paste this code into column C  ||| _ =IF(LEN(B1)>140,B1__,"")_  ||||

This should create in Column C a list with all the strings > 140

Hope this helps,

Cornel