A quick way to check the length of your copy

Formula Friday #9

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

This one is for all the Marketeers out there

The LEN Function will be your best friend whenever you need to check if your copy is long enough, or short enough, for your assets. I personally think it is best used in conjunction with conditional formatting. That way it’s easier to see if any amendments need to be made.

The LEN Function

Is mostly used to count a string of text, but it can count any character input, i.e. special characters such as /@!, numerical or non-numeric characters.

The formula would look like this =LEN([selected cell to analyse])

Len function example

For the eagle-eyed among you, you’ll have noticed the LEN function also counts the spaces within the text string as a character. Which is important to know, because you could have an instance where spaces are excluded from your copy restrictions.

Excel, forever being the helpful tool that it is. Has a way to count the characters in a string of text, and exclude the spaces.

The formula you will need in this instance is

=LEN(SUBSTITUTE([selected cell to analyse],” “,””)

Len function and substitute function for character count

Now that we can count the characters in a text string, including and excluding spaces. The only thing left to figure out is if we can count the words.

Thanks to the magic of Excel we can

All you need to do is combine the two formulas (or formulae) we have just outlined. And add 1. The formula is below, and we’ll talk about how it makes sense in a moment.

=(LEN([selected cell to analyse])-LEN(SUBSTITUTE([selected cell to analyse],” “,””))+1

Len and Substitute functions used to count the number of words in a sentence

This formula comes in handy when you’re checking if you have the optimal number of hashtags on an IG post. Assuming they are separate by a space in the copy doc you have created.

The easiest way to understand how this formula works, is to think of the word and the space between it and the next word as a pair; “word+space”. If you have a sentence made up of 5 words, you will have 4 spaces. 

That is how this part of the formula works.

=(LEN([selected cell to analyse])-LEN(SUBSTITUTE([selected cell to analyse],” “,””))

The result is the difference between the total character count, including spaces, and the character count excluding spaces.

In a sentence made up of 5 words, the answer for this formula is 4.

Because the last word is the only word in the sentence without a second half to it’s pair. I.e. there is no space between it and the next word. You need to add 1 back on, to account for that “lost pair”.

If I have lost you, it’s okay. It took me a while to get my head around it as well. Try it out for yourself a couple times and you will start to understand what I mean.

Below is an example to show how this formula can be used to count the number of hashtags for an IG post.

Example of hashtag count

Remember for this particular formula to work, they need to be separated by a single space.

Final thoughts

Excel has knocked it out of the park once again.

I focused this one on Marketeers because I know how critical it is to optimise your copy as much as possible. But it can work for Merchies too, to check if style codes have been added onto the line detail correctly for example.

If you found this useful make sure to find a way to use it right now, so you never forget it.

Until next time,


One reply on “A quick way to check the length of your copy”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.