Count specific text string in a cell
Question: How do I count how many times a text string exists in a cell value in Excel?
Answer:
The formula in cell C6 counts how many times a given text string is found in a cell value.
Formula in C6:
Explaining formula in cell C6
Step 1 - Substitute given text string with nothing
SUBSTITUTE(C2, C4, "")
becomes
SUBSTITUTE("AA BB CC AA CC BB CC AA", "AA", "")
and returns " BB CC CC BB CC ".
Step 2 - Count text string characters
LEN(SUBSTITUTE(C2, C4, ""))
becomes
LEN(" BB CC CC BB CC ")
and returns 17.
Step 3 - Count text string characters in cell C2
LEN(C2)
becomes
LEN("AA BB CC AA CC BB CC AA")
and returns 23.
Step 4 - Subtract original character length with new text string character length
LEN(C2)-LEN(SUBSTITUTE(C2, C4, ""))
becomes
23 - 17
and returns 6.
Step 5 - Divide with search string character length
(LEN(C2)-LEN(SUBSTITUTE(C2, C4, "")))/LEN(C4)
becomes
6/LEN(C4)
becomes
6/2
and returns 3 in cell C6.
Download Excel *.xlsx file
Count specific text string in a cell.xlsx
Related articles
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count multiple text strings in a cell range
The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
13 Responses to “Count specific text string in a cell”
Leave a Reply to Meaghan
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Thank you my new friend, I needed that. Nice idea to count the characters with and without the term and then to divide by the number of characters in the term.
Justin,
Thank you
Read more:
Count number of times a string exist in multiple cells using excel formula
Count multiple text strings in a cell range
What formula should I use to see how many times a phrase occurs within multiple cells?
Lee,
Count number of times a string exist in multiple cells
can we just print the count of every string(name) in a particular column in front of its name, in a normal table(not pivot table)
and display that in a diff sheet.
How can I do this for a cell that is on a different tab than my formula?
Change cell reference B3 in this formula:
=(LEN(B1)-LEN(SUBSTITUTE(B1, B3, "")))/LEN(B3)
Example:
=(LEN(B1)-LEN(SUBSTITUTE(B1, Sheet3!A1, "")))/LEN(Sheet3!A1)
I know that the substitute function does not support wildcards, so my question is:
Is there a way to count multiple times something like: "??.??.???? ??:??:??"
=(LEN(B1)-LEN(SUBSTITUTE(B1, "??.??.???? ??:??:??", "")))/LEN(B3)
Even if it is not using the substitute function is ok for my purposes.
PS. I cannot install any additional packages as for example REGEX.
Javier,
You don't need to install additional packages.
https://www.get-digital-help.com/2017/05/24/count-matching-strings-using-regular-expressions/
How to search multiple strings at a time in a cell.
For example: I want to see if Inc or Inc. or inc. is present in a cell
Depti,
I believe you are looking for this post:
https://www.get-digital-help.com/2012/03/28/search-for-a-text-string-and-return-multiple-adjacent-values/#multiple
Is there a way to have overlap in counting? I have the string nynynynyyynn and I want to count how many times the pattern nyn exists. When I use the formula provided it counts 2 because it doesn't reuse the n in the 3rd position (or at least that's what I think is happening). Is there a way to get this to be 3?
Meaghan,
Great question!
The substitute function deletes each substring "nyn" from the value, that is why it doesn't "reuse" the n because there is no n.
I made a new formula for you that I believe matches each instance even if overlapping, see this article:
https://www.get-digital-help.com/2018/04/17/count-a-given-pattern-in-a-cell-value/