Count multiple keywords in text

[HammerOz]

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.

[Vernon_Fowler]

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, https://wordcounter.net/ does both a count of total words, and lists words with their occurrence frequency.

[Karlstens]

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

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

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

[W_Vann_Hall · Accepted solution]

@HammerOz, @Vernon_Fowler, @Karlstens –

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…

[Vernon_Fowler]

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 https://wordcounter.net/, I’m seeing satisfactory word counts with counts between 40 and 140 words only differing by one or two words in most cases.

[Alan_Larsen1]

Hey, that’s very clever and helped me a lot. Thanks!