Counting The Number of Times a Keyword Appears in an Airtable Cell

Written By Ryan Octosa

Check out the following formula, where:

  • {Column} refers to the name of the column the cell of interest is in

  • “text” refers to the string of text that you want to count in the cell


(LEN({Column})-LEN(SUBSTITUTE({Column}, "text", "")))/LEN("text")

Let's take a look at the demo below where we try counting two keywords: "text" and "this".


 

From the screenshot above, observe how the formula is case-sensitive. That’s why if you were to look at the screenshot above under the column “this” (lowercase), the keyword counts yield 0 for all rows. To account for keywords in sentence-case only, you would have to change “text” into “Text” in the above formula:

(LEN({Column})-LEN(SUBSTITUTE({Column}, "Text", "")))/LEN("Text")

To account for keywords in both sentence-case and lower-case, you can combine both the above formula this way:

(LEN({Column})-LEN(SUBSTITUTE({Column}, "text", "")))/LEN("text")
+
(LEN({Column})-LEN(SUBSTITUTE({Column}, "Text", "")))/LEN("Text")

Ryan Octosa
Next

Surface Pro Suddenly Slow? Here’s One Way to Fix It.