Formula - Search how many times a certain word is found in a single cell

Platform discussions
1

Hey everyone. Does anyone know how to (using the Formula column) Search for a specific word in a single cell and get the number of times this word is present?

2

I haven’t tested at length but here is how I would do it.

Let’s say you want to check how many times a character appears in a text: you would do it by comparing the length of the text with the length of the same text after you remove the character.

This would translate into this for letter A:
LEN({Text})-LEN(SUBSTITUTE({Text},“A”,“”))

Now for a word, you would do the same divided by the length of the word:
(LEN({Text})-LEN(SUBSTITUTE({Text},“Word”,“”)))/LEN(“Word”)

Bear in mind that SUBSTITUTE is case-sensitive. So in the first formula we get the number of occurrences of A not a. If you want to count the number of occurrences of both, you’d need to replace {Text} by UPPER({Text}), i.e. to convert first the text to uppercase.

We are not done though because after testing, I found this issue:

Let’s say the text is “my boss is my friend” and you’re looking for the number of occurrences of “my”
Total Length: 20

You would think that the length after removing the 2 “my” is 16, but that’s not the case because Monday removes leading and trailing spaces. So the resulting text is not " boss is friend" but “boss is friend”. Monday removed the first space. Length is 15, not 16.

The workaround is to add a character before and after the text, like a quote, so that there is never a leading or trailing space. So we need to replace {Text} by CONCATENATE(“'”,{Text},“'”).

This is the case-sensitive version:
(LEN(CONCATENATE(“'”,{Text},“'”))-LEN(SUBSTITUTE(CONCATENATE(“'”,{Text},“'”),“Word”,“”)))/LEN(“Word”)

This is the case-insensitive version (need to make sure you convert the searched word to uppercase):
(LEN(CONCATENATE(“'”,{Text},“'”))-LEN(SUBSTITUTE(UPPER(CONCATENATE(“'”,{Text},“'”)),“WORD”,“”)))/LEN(“WORD”)

3

Hi @Tomas - you can use the following formula to count the occurrence of a word in a text column:

(LEN({String})-LEN(SUBSTITUTE({String},{Look For},“”)))/LEN({Look For})

image

Hope this helps!
Mark

1 Like
4

Thank you!!! Exactly what I needed! In fact, I’ve also got some help from monday.com and I was provided a very similar solution:

DIVIDE(MINUS(LEN({String}), LEN(SUBSTITUTE({String},“monday”,“”))),LEN(“monday”))

1 Like
5

Thank you Gilles!!

I’ve went with the other solution on this post as is simpler and fixed my problem! But thank you again!

6

thanks for that, helped me allot today