Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 28, 2020 04:59 PM
So I’m looking to employ a formula to provide a word count for my SEO keyphrases, which range from 2 to 8 words. Below is the formula that I thought would work, but I’ve had no such luck…does anyone have any thoughts as to what’s going on here? Thank you in advance!
IF(({Keyphrase}=BLANK()),0,(LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase},’ ', ‘ ’))+1))
Solved! Go to Solution.
Nov 29, 2020 05:59 AM
The formula that Justin provided will give you the number of comma separated items. Is your {Keyphrase} a comma separated list? Or is it a single phrase with a variable word count?
If it is a single phrase (no commas), and you want a word count of all the words in the field, you should remove the spaces instead of the commas.
IF(
Keyphrase,
LEN(Keyphrase) - LEN(SUBSTITUTE(Keyphrase, " ", "")) + 1,
0
)
Nov 29, 2020 12:50 AM
Welcome to the community, @Andrew_Roche! :grinning_face_with_big_eyes: There are a few issues with the formula you wrote. First all, all quotes need to be non-styled quotes. The forum automatically styles quotes in normal text, which is why it’s preferred to format formulas using the preformatted text option. This can be done by selecting the formula and clicking the preformatted text icon: </>
When properly formatted, quotes should be straight vertical quotes, not slanted or curly. Airtable won’t recognize the latter type.
It also looks like you’ve got an extra level of parentheses around your condition check, which is likely causing part of the problem. The beginning portion should look like this:
IF({Keyphrase}=BLANK(), ...
However, you can simplify the way you look for the {Keyphrase}
field’s contents by only listing the field name:
IF(FieldName, result_if_true, optional_result_if_false)
Anything in that field is equivalent to true, and an empty field is equivalent to false.
With that applied and the parentheses and quote issues fixed, this should work:
IF(Keyphrase, LEN(Keyphrase) - LEN(SUBSTITUTE(Keyphrase, ",", "")) + 1, 0)
Nov 29, 2020 03:24 AM
Hey @Justin_Barrett - Thank you for the response. However, the formula is still yielding a result of 1 across the board…thoughts?
Nov 29, 2020 05:59 AM
The formula that Justin provided will give you the number of comma separated items. Is your {Keyphrase} a comma separated list? Or is it a single phrase with a variable word count?
If it is a single phrase (no commas), and you want a word count of all the words in the field, you should remove the spaces instead of the commas.
IF(
Keyphrase,
LEN(Keyphrase) - LEN(SUBSTITUTE(Keyphrase, " ", "")) + 1,
0
)
Nov 29, 2020 08:28 AM
Thank you, @K_V!
Nov 30, 2020 09:04 AM
Thanks for the follow-up. I must admit, the different quote types in the original formula confused me. I thought it was just malformed and that the text was a comma-separated list.
Nov 02, 2023 04:24 AM - edited Nov 07, 2023 06:13 AM
I've dealt with similar issues before. It looks like you're close to the solution. Try this:
scssCopy code
IF({Keyphrase}=""", 0, LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase}, " ", """)) + 1)
I hope this helps you count those SEO keyphrases effectively! Also, you could use this service (www.pixelocity.com) to do all SEO jobs for you. Good luck!