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))
Best answer by kuovonne
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
)
+21Welcome 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)
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)
Hey @Justin_Barrett - Thank you for the response. However, the formula is still yielding a result of 1 across the board…thoughts?
+29The 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
)
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
)
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.
+1I used LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase}, " ", "")) + 1 to count the words, but added an IF to handle empty fields so it doesn’t show “1” for blanks.
+1Your formula looks close, but the issue is often the type of quotes—curly quotes can break it, and spaces can sneak in weird ways. Switching to straight quotes and double-checking for extra spaces usually fixes it. At aeo, we had a spreadsheet that kept tripping on multi-word phrases, and just cleaning up the quotes and spacing made everything click.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.