Excel Tidbits: Limiting Meta Descriptions Characters and Words Like Google

I found it hard to find this information on the web, so after a little bit of work I created an Excel formula that mimics the cut off point Google may naturally use when displaying your page’s Meta description in search engine results pages (SERPs). This can come in handy when you are optimizing your site for search engines (SEO). A well written page description is very helpful in improving a site’s click through rate. Depending on your page’s subject and target audience, writing a description in which the page’s story is incomplete or leaves a person hanging improves the searcher’s interest in clicking through. As a result, the last few words of a description could play a large part in creating that situation. But if Google cuts it off too soon or too late, it may be just enough to lose a person’s interest.

Here is the Formula:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Example:

Cell Contents: Everyone loves Google and wants to write just like them. Cutting off descriptions at the same spot as Google can help you write really great descriptions. Check out this awesome formula.

The Look in SERPs:

MS Excel Tip: Limiting Meta Descriptions Number of Characters
/ms-excel-tip-limit-meta-descriptions-characters-and-words-like-google
Everyone loves Google and wants to write just like them. Cutting off
descriptions at the same spot as Google can help you write really great …

Behind the Formula

1. The LEFT functions used in the internal portions of the formula grabs only the max amount of characters Google will show (154) and uses that throughout the formula for calculating.
2. The formula then removes all the spaces and counts the character differences between the regular set and the one without spaces (this can also be used to count the number of words). This tells us how many spaces are in that 154 character portion of the description.
3. Using that number, it replaces that final occurrence of the space with an asterisk using the SUBSTITUTE function (since it’s unlikely that an asterisk would be used in a description).
4. Then using a FIND function, it counts the number of characters to that point of the asterisk.
5. It uses the LEFT function to only return the characters up to the character count where the FIND function found the asterisk.
6. And lastly, all of  that only happens if the description is over 155 characters.

Use it well… 🙂