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])
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],” “,””)
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
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.
Remember for this particular formula to work, they need to be separated by a single space.
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,