With various SEO tasks it is often the case where you need to count the number of occurrences of text in a cell. Unfortunately there isn’t a nice and simply formula to do this, but there is a work around.
Take the example if you download all of the duplicate title tags from a website from Google Webmaster Tools. The way that Google presents this data is in a two column format as seen below;
As you can see in the image above, this isn’t that useful in itself as often the pages with duplicate title tags can be the same page simply with parameters appended. These pages can often be listed a number of times if it is something like a session ID as the parameter which Google has managed to identify. What would be nice is if Google actually listed a number next to the list which is downloaded telling you how many pages the title tag is duplicate over as this would be a nice starting point to prioritise fixing all of those issues – unfortunately they don’t.
Instead you have to use a bit of Excel Magic to calculate this information. Fortunately within the downloaded spreadsheet, the URLs listed in column B are separated by the pipe symbol ‘|’ which can be used as part of the formula below;
=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,”text”,””)))/LEN(“text”)+1
So what does this mean? Well if we put this formula into cell C2 following on from the original example above, then this is what it would mean….
=SUM(LEN(B2)-LEN(SUBSTITUTE(B2,”|”,””)))/LEN(“|”)+1
What the formula is doing is counting the number of occurrences of the pipe symbol ‘|’ then adding 1 to that number. Why adding 1? Because if two URLs in (this example) are listed in cell B2, then there will only be 1 pipe symbol and we want to know how many URLs the title tag is duplicated over.
Below is the final example of how this looks when implementing this solution;
Now that you have the formula for doing this work, you can easily apply this same logic to any similar tasks where you need to count the number of occurrences of text contained within a cell in Excel.