Count multiple keywords in text


Hi all, I am wondering if I had a long text field and then a multi select field for key words, could I create a formula to show each key word and a count of it’s occurrence in the text field.


A word count function would be very useful. Finding occurrences from matches in a multi-select field would be super handy for text analysis. So far I’m not getting much value out of multi-select fields - this capability would improve that!

A work-around in the meantime, does both a count of total words, and lists words with their occurrence frequency.


In double checking the Airtable Field Reference now, I’m surprised there isn’t even a string character count function, as even counting spaces " " would give a pretty good indication of word count.

Airtable devs, would love to see this feature of a String Character Count added!


@HammerOz, @Vernon_Fowler, @Karl_Hosking1

I can get you halfway (the easy half) there, but using the inputs from a multi-select field as your keywords is problematic.

The easy part is this: The basic formula for counting the number of occurrences of {Word} in {String} is

(LEN({String}) - LEN(SUBSTITUTE({String},{Word},"")))/LEN({Word})

That is, you want to subtract the length of {String} with all instances of {Word} deleted. This provides you with the combined length of all instances of {Word} in {String}. Dividing that number by the length of {Word} gives you the number of instances.

As I noted, this is by far the easiest part of the task. Since Airtable does not [yet ;-)] provide any mechanism for iterating through a variable number of loops, you’ll essentially have to craft an elaborate chained IF() that takes into consideration all potential multi-select values, and that IF() statement will need to be actively maintained, requiring modification after every new addition to the multi-select.

Hey, but the other part: I’ve got you covered…

Counting Records that Meet a Criterion

I tried this with the space character " " as my {Word} to see if it’ll produce a word count on a long text field.

(LEN({MyLongTextFieldname}) - LEN(SUBSTITUTE({MyLongTextFieldname}," ","")))/LEN(" ")

Comparing results with, I’m seeing satisfactory word counts with counts between 40 and 140 words only differing by one or two words in most cases.