Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 18, 2017 07:28 PM
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! Go to Solution.
Sep 26, 2017 06:08 AM
@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…
Aug 08, 2017 05:51 AM
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.
Sep 09, 2017 12:28 AM
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!
Sep 26, 2017 06:08 AM
@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…
Oct 15, 2017 01:34 PM
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.
Dec 18, 2018 04:24 PM
Hey, that’s very clever and helped me a lot. Thanks!