How to highlight a excel cell in red if more than 140 chars in it?
-
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?
-
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=1620525099Your welcome,
Chris -
For gDocs:
Create extra column and add formula =len(cell ref containing text)
Add conditional formatting to the resulting number where greater than 140
-
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
-
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
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
My posts are ignored by google not sure why
I have been investigating this problem for some time now and there must be a technical problem. My posts seem to be ignored by google. For example, if i put this title of my article in google, other sites come up instead of my website Lincolnshire Caravan Owners Struggle To Compete With Haven https://www.in2town.co.uk/skegness-news/lincolnshire-caravan-owners-struggle-to-compete-with-haven/ I am trying to find out what technical problem I have that is stopping google displaying my post. Can anyone advise me on what tools to use and how to find out what is going wrong
Technical SEO | | headlinesplus0 -
FAQ Schema Markup
I was wondering which blog posts would qualify for an FAQ Schema markup. For instance, we have a blog post which is more like a Q&A interview with our customer where our product gets mentioned several times. Would we get dinged for including our product name in the markup? First of all does that kind of blog posts even qualify for the markup? Example of the blog post: https://www.revulytics.com/blog/qa-techsmith-snagit-strategy-lead-daniel-foster
Technical SEO | | revulytics0 -
Internal link structure for my loan website
Hi folks. I own a Norwegian consumer loan/financing website, which has been monetized with links. I've created various silos for my content, according to what I believe is most relevant to the user.
Technical SEO | | llevy
However, as a result each article now has a sidebar list, which in turn links to all other articles within the same category (silo). As you can see here, it has about 30 links in the sidebar: forbrukslån.no/beste-lån. With 30 articles in a silo, that corresponds to over 900 internal links, in just one silo alone. I wonder if this could be hurting me SEO wise? I know G cares a lot about relevance and user experience. So I have a feeling it could be interpreted as spammy. Reason I did this in the first place, is that the header links are also being repeated on all pages, without any issue. T4FHxHw0 -
Very wierd pages. 2900 403 errors in page crawl for a site that only has 140 pages.
Hi there, I just made a crawl of the website of one of my clients with the crawl tool from moz. I have 2900 403 errors and there is only 140 pages on the website. I will give an exemple of what the crawl error gives me. | http://www.mysite.com/en/www.mysite.com/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/en/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/en/en/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/index.html#?lang=en | http://www.mysite.com/en/www.mysite.com/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/en/index.html#?lang=en | | | | | | | | | | There are 2900 pages like this. I have tried visiting the pages and they work, but they are only html pages without CSS. Can you guys help me to see what the problems is. We have experienced huge drops in traffic since Septembre.
Technical SEO | | H.M.N.0 -
Data Highlighter doesn't show page
We have an event related website http://www.sbo.nl so i wanted to use data highlighter because most of our event pages are the same. But data highlighter doesn't show those pages, I will see only an empty page. For example http://www.sbo.nl/veiligheid/brandveiligheid-gebouwen/ Does someone of you understand what is going on. Data highlighter does show the homepage. I am thinking it is maybe because of tabbed browsing, or the chat function in that page. Hope someone can help. You can see a screenshot of Data Highlighter http://www.clipular.com/c?6659030=2X2gQv4O8_9RzcZ1Hk_7xGtCPYo&f=d40975c80bdd11dc357f050cafa73a80 I hope someone can help because i am lost 🙂 Cheers Ruud
Technical SEO | | RuudHeijnen0 -
Find broken links in Excel?
Hello, I have a large list of URL's in an excel sheet and I am looking for a way to check them for 404 errors. Please help! Adam
Technical SEO | | digitalops0 -
DISQUS COMMENTS backlinks-good for seo? YES/NO?
DISQUS COMMENTS backlinks-good for seo? YES/NO? I have just started commenting on "powered by disquus" websites in the Disqus comments box and left a link to my website in the name field! Having googled whether Disqus comments backlinks are any good for seo purposes i have discovered that there is a 50/50 view on the subject with some people saying they are a "goldmine" for getting high PR backlinks and others saying they are a waste of time because googlebot cannot read Java. My own experience of commenting on Disqus powered websites is that wordpress blogs powered by disqus comments ARE INDEXED by GOOGLE and the "BACKLINK IS IN THE SOURCE OF THE PAGE" When i comment on normal websites using the Disqus comment system i have found that my Disqus comments ARE NOT indexed by Google and there IS NO BACKLINK in the page source! Has anybody got any views on whether Disqus comments backlinks are any good?
Technical SEO | | Freebetsuk2 -
What tool do you use to check for URLs not indexed?
What is your favorite tool for getting a report of URLs that are not cached/indexed in Google & Bing for an entire site? Basically I want a list of URLs not cached in Google and a seperate list for Bing. Thanks, Mark
Technical SEO | | elephantseo3