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.
Get Excel *.xlsx file
Count specific text string in a cell.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
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
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
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 […]
14 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.
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/
how to write
in cel a1 to to cell b2
5489 to 4589 what formula to use to arange number in smallest to largest