Microsoft Excel, unlikely Word, doesn’t provide a feature to count how many characters are contained into a cell.
A spreadsheet has a different scope, after all, so we can’t in theory complain with this. However, more than often a spread sheet is the best tool to be used, especially when to support the final considerations a “number” is required.
During my last SEO exercise, I was after a very simple “tool” that given a list of phrases it would have been able to return the numbers of characters and words forms the phrase, and which is the proximity of a specific keyword within the text supplied.
To accomplish the above task, the only ready-to-go function available in Excel is LEN, which returns how many characters are in a cell. The usage is pretty simple:
=LEN(REF)
Where you should replace the REF with the reference to the cell you are interested to discover the content length.
What about the rest?
How many words have my title tag?
To help myself with the rest of the task, the SUBSTITUTE and TRIM functions have been required.
As you can imagine, the SUBSTITUTE is in charge to replace a string into a context, while the TRIM removes the trailing spaces at begin and the end of the text. So anything that is really complicate.
The SUBSTITUTE syntax is: SUBSTITUTE (TEXT_TO_SEARCH,WITHIN,NEW_TEXT,[TIMES]) while for the TRIM you need to use TRIM(REF), exactly as you did for the LEN function.
Therefore, TRIMming a string like “ hi how are you “, this will become “hi how are you” (the spaces are removed).
Now, let’s try to assemble everything and coming up with a full formula to calculate how many words are contained into a text-cell.
=LEN(TRIM(REF))-LEN(SUBSTITUTE(TRIM(REF)," ",""))+(LEN(REF)>1)
Just remember to substitute the REF with the cell reference you are interested in.
Compute the keyword density (of the meta tag title)
The hard task of this exercise was to calculate the keyword density of my meta tag title. In this specific circumstance, I’m talking about the meta tag title, but of course the formula I’m providing is very generic and can be applied to every text you need.
However, I won’t recommend it’s usage for long text or paragraphs, a more performing VBA routine can be written.
Of course, I wasn’t struggling from a mathematical point of view, rather on how to obtain the results in a “simple” formula that could have been copied and pasted easily across the whole spread sheet, without using macros or VBA code.
So, partially using the previous formula, to obtain your density.
=(LEN(TRIM(MID(A2,1,IF(ISERR(FIND(B2,A2)), 0, FIND(B2,A2)))))-LEN(SUBSTITUTE(MID(A2,1,IF(ISERR(FIND(B2,A2)), 0, FIND(B2,A2)))," ",""))+IF(ISERR(FIND(B2,A2)), 0,1))/C2*100
The formula above contains also an error check, just in case, but has the limit to be case sensitive.
Therefore, if your text is something like “bye bye” and I’m looking for “bye”, my keyword density will be 50%, which is the basilar concept applied by each search engine while individually assessing the meta tags.
Doing something more complex was outside the scope of my work, however I’ve provided the basis you require for expanding your keyword assessment routine.
A conclusive note before leaving. In case the destination celli s formatted as a percentage, please remember to add a “/100” (without quotes) at the end of the formula, otherwise you won’t never get a percentage value.