SEO Keyphrase Word Count Formula

[Andrew_Roche]

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))

[Justin_Barrett]

Welcome to the community, @Andrew_Roche! 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)

[Andrew_Roche]

Hey @Justin_Barrett - Thank you for the response. However, the formula is still yielding a result of 1 across the board…thoughts?

[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
)

[Andrew_Roche]

Thank you, @Kuovonne_Vorderbrugg!

[Justin_Barrett]

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.