Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Count multiple keywords in text

Solved
Jump to Solution
10422 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!