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.
Solved
Count multiple keywords in text
Best answer by W_Vann_Hall
@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…
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!
@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…
W_Vann_Hall wrote:
@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.
W_Vann_Hall wrote:
@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…
Hey, that’s very clever and helped me a lot. Thanks!
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.