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 -
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 -
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 -
Structured Data verses Data Highlighter Tool
Hello, I would like to implement structured data on a site, and I am a newbie in this. (I know, hard to believe considering it's 2015.) I'm slightly confused on why would I use structured data, if I can just tag the different elements with the data highlighter tool in Google Webmaster Tools. Should I focus on one or the other? The sites I would be working on would be considered a "local business" and have the basics (i.e. phone, address, hours, etc).
Technical SEO | | sumoleap0 -
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 -
Getting listed in DMOZ after a red mark against me?
Hi everyone, Before I start, I want to point out that I realise DMOZ is not everything. If I can't get a link, then I'll accept it, but I'd like to try damn hard to get a link in there if possible as I honestly believe we are a reputable company that deserves to be in there with other companies in our region. So, lets go back to the start: A few years ago, when I was young and foolish, I posted a request on rentacoder (now vworker) asking for someone to submit us to DMOZ for $xxx. If I remember correctly, we accepted someone for the job and they submitted our site. Since then, we've never been accepted. I tried submitting the site a couple of years ago and realised it still hadn't been submitted around a year ago. I thought this was a bit strange, so I did a bit of digging. I ended up talking to someone who was a DMOZ editor. They looked at my listing on DMOZ and basically said that I'd be rejected for 'bribing an editor' - something that I still maintain I didn't do. Never at any point have I said to someone that I would pay them to guarantee us a listing. At the time, I thought there was a 'certain way' to write a submission, but now that I've grown up a bit, I've realised it's fairly easy to get a listing if you write a well written description and are honest - I've done this for many clients sites which are now accepted. Unfortunately, this does mean that It looks like I won't be able to get our agency on DMOZ, but I just wanted to give one last push and see if anyone on SEOMoz knows of any one I could speak to in order to lift this ban? I realise now that it was probably a stupid thing to do, and in hindsight, I should have just waited like everyone else. I hold my hands up to that and I've worked over the last few years to grow our agency into something special for our clients. As I say, I realise dmoz isn't everything, but it is a directory that still holds value and I would like to be linked off there if possible. Any tips on how I may be able to beg for my ban to be lifted would be much appreciated. Kind regards, Lewis PS: I applied to become an editor at one point, this was when I realised something wasn't right - http://www.resource-zone.com/forum/index.php?showtopic=52658
Technical SEO | | PinpointDesigns0