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
-
What Tools Should I Use To Investigate Damage to my website
I would like to know what tools I should use and how to investigate damage to my website in2town.co.uk I hired a person to do some work to my website but they damaged it. That person was on a freelance platform and was removed because of all the complaints made about them. They also put in backdoors on websites including mine and added content. I also had a second problem where my content was being stolen. My site always did well and had lots of keywords in the top five and ten, but now they are not even in the top 200. This happened in January and feb. When I write unique articles, they are not showing in Google and need to find what the problem is and how to fix it. Can anyone please help
Technical SEO | | blogwoman10 -
What Tools Do I USe To Find Why My Site No Longer Ranks
Hi, I made the mistake of hiring a freelancer to work on my website [in2town.co.uk](link url) but after having a good website things went from bad to worse. The freelancer was kicked off the platform due to lots of compliants from people and creating backdoors to websites and posting on them. It cost me money to have the back door to our site closed. I then found lots of websites were stealing my content through the rss feed. Two of those sites have now been shut down by their hosting company. With all these problems I found in Feb that the hundreds of keywords that I ranked for had vanished. And all the ones that were in the top ten for many years have also vanished. When I create an article which includes https://www.in2town.co.uk/skegness-news/lincolnshire-premier-inn-staff-fear-for-their-jobs/ they cannot be found in Google. Normally before all these problems, my articles were found straight away. If I put in the title name Lincolnshire Premier Inn Staff Fear For Their Jobs and then add In2town in front of it, then instead of the page coming up with the article, it instead shows the home page. Can anyone please advise what tools i should be using to find out the problems and solve them, and can anyone offer advice please on what to do to solve this.
Technical SEO | | blogwoman10 -
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 -
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 -
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 -
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 -
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 -
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