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
-
cross canonicalization with redirect
I'm working with a website that has turned one of its pages into its own website within the main website - mostly for the ease of customers, making it simpler to access that page using www.page.com rather than www.mainsite.com/about/page.
Technical SEO | | Shrine.SEO.Gal
As a result, there are two urls for that page (the ones just mentioned), both pointing to the exact same page, but with different urls. Now, they have made it so www.mainsite.com/about/page permanently redirects to www.page.com. which I thought was a good call. However, what do I do about canonicalization? Is it good to point the canonicalization of www.page.com to www.mainsite.com/about/page so that the rankings and link equity are maintained in the main website? Or would the fact that the www.mainsite.com/about/page redirects to www.page.com mess that up? I hope this makes sense!0 -
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 -
Recently rankings for "Tree Service" dropped
Hi, we've had a page which was ranking top 1 on the Google rankings but has complete dropped out to 5. We don't have any duplicate errors from that page on here. Do you have any suggestions?
Technical SEO | | FIT0 -
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 -
Google Not Picking Up Posts
I am trying to work out why from March 4th Google is not seeing my posts. Our google impressions have dropped from 8,000 to 40. If you put in the full article name with speach marks it does not find it, and instead shows the home page in google. We have not had any warnings. We did have work done on our site but nothing else i could think of to cause this. Can anyone let me know what may have caused this. All articles are original
Technical SEO | | headlinesplus0 -
URLs dropping from index (Crawled, currently not indexed)
I've noticed that some of our URLs have recently dropped completely out of Google's index. When carrying out a URL inspection in GSC, it comes up with 'Crawled, currently not indexed'. Strangely, I've also noticed that under referring page it says 'None detected', which is definitely not the case. I wonder if it could be something to do with the following? https://www.seroundtable.com/google-ranking-index-drop-30192.html - It seems to be a bug affecting quite a few people. Here are a few examples of the URLs that have gone missing: https://www.ihasco.co.uk/courses/detail/sexual-harassment-awareness-training https://www.ihasco.co.uk/courses/detail/conflict-resolution-training https://www.ihasco.co.uk/courses/detail/prevent-duty-training Any help here would be massively appreciated!
Technical SEO | | iHasco0 -
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