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
-
Need help in diagnosing what I may be doing wrong
I have a site that has been having problems ranking. Initially, spam rate was at 18%. I have since changed the URL and forwarded to the original so now the spam rate is under 5%. Phone calls started picking back up very slowly but then by August 2024 things came to a screeching halt. Phone has been dead and very little business has been written. I did notice on the robots.txt file it had this: User-agent: *
Technical SEO | | SOM24
Disallow: /
User-agent: Googlebot
Disallow:
User-agent: bingbot
Disallow: /no-bing-crawl/
Disallow: wp-admin and now I have since changed it to this:
User-agent: *
Disallow: /wp-admin/
Allow: /wp-admin/admin-ajax.php Sitemap: https://urlishere.com/sitemap_index.xml My question is what else do I need to do to get this site to start ranking again? We are blogging once a month, writing press releases once a month, updating the social media a few times a week. I feel like maybe there is something on the backend that needs to be done to get this site back to ranking. I am using SEO by Yoast and I have filled in the title and meta description fields for all pages. There is a spot in Yoast where I can validate the site with Google, Bing, etc. I'm trying to figure out how to do that. I do see in the site's Google Webmaster Tools there are several pages not indexing. Any ideas on what else I can do to get this site to start ranking again? Thank you.0 -
How to fix core web vital issue on shopify website , any recommned app from shopfiy store?
I'm facing challenges optimizing Core Web Vitals on my Shopify store. Does anyone have experience with Shopify apps that effectively address LCP, FID, and CLS issues? Any specific recommendations would be greatly appreciated.
Technical SEO | | faizalialiali0 -
Product photo links appear in search results
Since I'm new, I don't have any information. Product photo links appear in the search results. Is this a problem? because when you click on the link, it does not take you to a page, but to a blank page with only a photo. What should I do for this situation?
Technical SEO | | UgurKarabulut0 -
How to Boost Your WordPress Website Speed to 95+ (Without Premium Plugins)
I'm reaching out for some advice on improving my WordPress website's speed. I'm currently using a free theme for this fusion magazine and aiming for a score of 95+ on Google PageSpeed Insights. I'm aware that premium plugins can significantly enhance performance, but I'm hoping to achieve similar results using primarily free solutions and manual optimizations.
Technical SEO | | mohammadrehanseo0 -
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 -
Will Google Data Highlighter support other schemas?
At the moment I see that the Data Highlighter supports a few schemas such as Events, Products, Articles, Restaurants etc. My client has a training division and runs regular training courses so we want to highlight those using the tool as "Training Courses," but this is not an option. Does anyone know why the tool doesn't support more categories and if there are plans to expand what it supports? I realise it would be better to use actual HTML markup onto the client site but their website is administered by their corporate parent in another country and they are not prepared to add in a Wordpress plugin to allow this. But the UK division, that we work for, wants to use it. We have restricted access to the Wordpress site so we don't have the access rights to add in plugins ourselves otherwise it would be no problem to do this.
Technical SEO | | mfrgolfgti1 -
Google will index us, but Bing won't. Why?
Bing is crawling our site, but not indexing it, and we cannot figure out why -- plus it's being indexed fine in Google. Any ideas on what the issue with Bing might be? Here's are some details to let you know what we've already checked/established: We have 4 301’s and the rest of our site checks out We’ve already established our Robots is ok, and that we are fixing our site map/it's in fine shape We do not see anything blocking bingbot access to the site There is no varnish or any load balancers, so nothing on that end that would be blocking the access We also don't see any rules in the apache or the .htaccess config that would be blocking the access
Technical SEO | | Alex_RevelInteractive0 -
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