Count a specific text string in a cell
Table of Contents
Count a 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
2. 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 exist multiple times in a cell, each instance is counted.
Answer:
Array formula in cell B11:
How to create an array formula
- Copy array formula (Ctrl + c)
- Select cell B11
- Paste array formula (Ctrl + v) to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys
You can also use this formula to count how many times a specific character exists in a column in excel.
Explain array formula in cell B11
=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
Step 1 -Â Replace existing text strings with new text string in named range tbl (A1:A6)
=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
Substitute(text, old_text, new_text, [instance_num]) replaces existing text with new text in a text string
SUBSTITUTE(tbl, $B$9, "")
becomes
SUBSTITUTE({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}, "AA", "")
returns this array:
{"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB Â DD"}
Step 2 - Return the number of characters in the named range tbl (A1:A6) without text string "AA"
=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))
becomes
SUM(LEN({"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB Â DD"}))
and returns 44
Step 3 - Return the number of characters in the named range tbl (A1:A6)
=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
SUM(LEN(tbl))
becomes
SUM(LEN({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}))
becomes
SUM({2;9;12;10;9;10})
returns 52.
Step 4 -Â Return the number of characters in cell B9
(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
LEN($B$9)
becomes
LEN("AA")
returns 2.
Step 5 -Â All together
=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)
becomes
=(52-44)/2)
becomes
=8/2
returns 4.
Named ranges
tbl (A1:A6)
What is named ranges?
Get excel example file
count-text-string-in-a-column.xls
This blog article is one out of three articles on the same subject.
- Count number of times a string exist in multiple cells using excel formula
- Count text that occurs multiple times in excel cell
- Count occurences of a specific text string in a column in excel
3. 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 if it overlaps another match.
Formula in cell B6:
A regular count would result in 2 matches, see picture below.
You can find the formula here:Â Count specific text string in a cell
A count where overlapping is allowed returns 3 matches and this is what is demonstrated in this article.
Explaining formula in cell B6
Step 1 - Build an array from 1 to the number of characters in the cell value
The LEN function counts the number of characters in cell B3.
LEN(B3) returns 13.
The INDEX function returns a cell reference based on a row number.
ROW(A1:INDEX(A1:A1000, LEN(B3)))
becomes
ROW(A1:INDEX(A1:A1000, 13))
becomes
ROW(A1:A13)Â and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}
The ROW function returns the row number of a cell. If a cell range is used the ROW function returns an array of row numbers.
Step 2 - Extract all possible substrings from cell value
MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))
becomes
MID(B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, LEN(D3))
becomes
MID("nynynynyyynn", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 3)
and returns the following array:
Step 3 - Check if substring is equal to search string
MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3
becomes
{"nyn"; "yny"; "nyn"; "yny"; "nyn"; "yny"; "nyy"; "yyy"; "yyn"; "ynn"; "nn "; "n "; " "}=D3
and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Step 4 - Convert boolean values to the corresponding number
The SUMPRODUCT function can't handle boolean values so the SIGN function converts them into numbers. TRUE = 1 and FALSE = 0.
SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3)
becomes SIGN({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})
and returns {1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}.
Step 5 - Count values in array
SUMPRODUCT(SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3))
becomes
SUMPRODUCT({1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0})
and returns 3 in cell B6. 1 + 0 + 1 + 0 + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 3.
Get Excel *.xlsx file
Count overlapping text string in a cell.xlsx
Count category
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
Excel categories
15 Responses to “Count a specific text string in a cell”
Leave a Reply
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
Can a date range be added to this formula? I need to find it between two dates.