How to count the number of times a keyword appears in an Airtable cell.

Check out the following formula in turquoise, 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")

Now, 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")

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

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