Help

Count multiple keywords in text

Solved
Jump to Solution
8535 5
cancel
Showing results for 
Search instead for 
Did you mean: 
HammerOz
7 - App Architect
7 - App Architect

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.

1 Solution

Accepted Solutions
W_Vann_Hall
13 - Mars
13 - Mars

@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…

See Solution in Thread

5 Replies 5
Vernon_Fowler
7 - App Architect
7 - App Architect

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.

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!

W_Vann_Hall
13 - Mars
13 - Mars

@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…

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.

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