Prev: Opening a sheet
Next: How do I create a formula to remove dashes?
From: nc on 5 May 2010 09:41

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.
From: Jacob Skaria on 5 May 2010 09:53
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)


--
Jacob (MVP - Excel)


"nc" wrote:

>
> 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.
 | 
Pages: 1
Prev: Opening a sheet
Next: How do I create a formula to remove dashes?