Count number times text appears in string

N

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.
 
J

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count no of times a letter occurs in a text string 4
Count Number Of Times Values Appears Consecutively, Return Longest 5
How to count the number of occurrence within string? 2
Excel Typed text appears in wrong cell 0
using vbCrLf in email body does not work with HTMLBody 2
Using a sub string in a COUNTIF 2
count a character 1
Counting consecutive strings of text 2

Top