Count number times text appears in string

[nc]

A
1 Module: C126, Module: C130, Module: C138, Module: C105


Can any help me write a function that would return the count of a specified
text in a cell.

For example for the string in cell A1 I would like to know how many times
does the text 'Module' appear.

 

[Jacob Skaria]

Try

=(LEN(A1)-LEN(SUBSTITUTE(A1,"Module",)))/LEN("module")

OR with the text string in cell B1
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,)))/LEN(B1)


----------------------------------------------------------------------------
If you want to have an word Match try the below formula. In the example you
have provided the exact word would be

Cell B1 contains "Match:"

=(LEN(A1)+2-LEN(SUBSTITUTE(" " & A1 & " "," " & B1 & " ",)))/(LEN(B1)+2)