Here is the formula, where {Column} refers to the name of the column the cell of interest is in, and “text” refers to the string of text that you want to count in the cell.
(LEN({Column})-LEN(SUBSTITUTE({Column}, "text", "")))/LEN("text")
Note that the formula above is case-sensitive, so 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")
Connect with Ryan
Find Ryan from DoorToRiches on X (Twitter), Threads, and Reddit for more finance content. Feel free to say hi!
