Count a specific text string in a cell
Table of Contents
1. 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. The count is case sensitive meaning AA counts AA but not lower case aa, or aA etc.
Formula in C6:
1.1 Explaining formula in cell C6
Step 1 - Substitute given text string with nothing
SUBSTITUTE(C2, C4, "")
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.
1.2 Get Excel *.xlsx file
Count specific text string in a cell.xlsx
2. Count text string in a cell 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:
2.1 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.
2.2 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)
Substitute(text, old_text, new_text, [instance_num]) replaces existing text with new text in a text string
SUBSTITUTE(tbl, $B$9, "")
returns this array: {"";"BB CC B";... ;"NNBB Â DD"}
Step 2 - Return the number of characters in the named range tbl (A1:A6) without text string "AA"
SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))
and returns 44
Step 3 - Return the number of characters in the named range tbl (A1:A6)
SUM(LEN(A1:A6))
becomes
SUM({2;9;12;10;9;10})
returns 52.
Step 4 -Â Return the number of characters in cell B9
(SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6, $B$9, ""))))/LEN($B$9)
LEN($B$9)
becomes
LEN("AA")
returns 2.
Step 5 -Â All together
(SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6, $B$9, ""))))/LEN($B$9)
becomes (52-44)/2) becomes 8/2 and returns 4.
2.3 Get Excel file
count-text-string-in-a-column.xls
3. Count a given pattern in a cell value - overlapping allowed
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.
A count where overlapping is allowed returns 3 matches and this is what is demonstrated in this article.
3.1 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; ... ; 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))
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
returns {TRUE; 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)
returns {1; 0; 1; ... ; 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.
3.2 Get Excel *.xlsx file
Count overlapping text string in a cell.xlsx
4. 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 to be an exact match but case sensitive. Column A1:A15 is the cell range.
Answer:
Cell E2 is the search string. In cell E3 an array formula counts the number of times the search string is found in cell range A1:A15.
Case sensitive formula in cell E3:
Explaining formula in cell E3
Step 1 - Count characters in each cell
The LEN function counts characters in a cell.
LEN(B2:B16)
returns {3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}
Step 2 - Substitue search string with nothing in all cells
The SUBSTITUTE function lets you replace a text string with another text string in a cell value or cell range.
SUBSTITUTE($B$2:$B$16, $E$2, "")
returns {"BBA";"CAC";... ;"BBCB"}
Step 3 - Count characters in array
LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))
returns {3;3;... ;4}
Step 4 - Subtract arrays
LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))
returns {0;0;4;... ;0}
Step 5 - Divide with cell length
(LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2
returns {0;0;0.5;...;0}
Step 6 - Sum number sin array
The SUMPRODUCT is better in this case because you are not required to enter the formula as an array formula to do the calculations.
SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2))
returns 5 in cell E2.
Case insensitive formula in cell E3:
Array formula in cell E6:
Get *.xlsx file
string exist in multiple cells.xlsx
Count category
Table of Contents Count rows with data Count non-empty rows Count cells between two values Count cells based on a […]
Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
Excel categories
49 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.
Awesome. It is simply superb
How about:
=SUM(N(ISNUMBER(FIND(D1,A1:A15))))
David Hager,
Yes, your formula works!
Thanks for commenting.
Hello. I used this formula and is very useful. But how will look formula to search the exact string (an not only string who include) in column A? Also if i have a value in column B named price, i want to return the value from columb B associated to row of string searched in column A. How to make tthis? Thank you.
Adriano,
Read this post: https://www.get-digital-help.com/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/
Hi! can you do this to count the number of "yes"'s in column B if column A meets the requirement of being "c"
A B
a yes
a no
b yes
b no
b no
b yes
c yes
c yes
c no
d yes
d yes
Thanks!
@Arielle,
You can use the SUMPRODUCT function to do that...
=SUMPRODUCT((A1:A1000="c")*(B1:B1000="yes"))
Adjust the ranges as needed (but make sure they are both contain the same number of cells).
Hello,
I would like to do this, but with parts of a string, is it possible?
EX:
Column A | Column B | Column C
545 contas-investimento
545 contas-Bolsa
546 contas-investimento
545 contas-investimento
Like, find how many times 545 has investimento.
Thanks.
Pedro Falcão,
Formula:
=COUNTIFS(A1:A4,E2,B1:B4,"*"&E1&"*")
Thank you for commenting!
Oscar, thank you so much.
Unfortunatly the excel i have instaled for now is the 2003, only by the end of this year my company wil install the most recent, then i will be able to use the formula you gave me.
Is there any other way to do this in excel 2003?
Pedro Falcão,
try this array formula:
=SUM(COUNTIF(E2,A1:A4)*NOT(ISERROR(SEARCH(E1,B1:B4))))
Don´t forget to enter it as an array formula:
1. Press and hold CTRL + SHIFT
2. Press Enter
Works like a charm!!!
Thank you so much.
Hi,
this formula is working only the data where in columns (A1:A15), i required the formula of the values in rows(A1:AZ1)
please help....
Cheran,
Try
Remember, it is an array formula.
Excellent!! But may I know is there is way I can highlight the cells
Prashant
Yes, try this CF formula:
=FIND($D$1,A1)
I have a HUGE list at the moment, and the formula stops working when changing the $A$1:$A$15 to $A$1:$A$8348. Here's what my formula looks like:
=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)
What am I doing wrong?
Haval,
did you create an array formula?
You know if you examine the formula in the formula bar. The formula is surrounded by curly brackets: {=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)}
When I use this formula. it does not work
Don,
Did you create an array formula?
I have added new instructions to this post.
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
It is good but now is case sensitive. How to do other away ?
Bob,
I have added a case insensitive formula to this post.
Thanks for commenting!
[...] Count2 Formulas to count the occurrences of text, characters, or words in Excel for Mac Count3 Count number of times a string exist in multiple cells using excel formula | Get Digital Help - Micr... I hope this resolves the problem for you, if not then I am sorry but I cannot help you further [...]
Dear Oscar,
Can it be possible ,Text of one cell filled up to others with the refference of number value entered in a other cell.i.e
A B
1 APPLE 5
Then
A B
1 APPLE 5
2 APPLE
3 APPLE
4 APPLE
5 APPLE
Amit,
I am not sure I understand.
=IF(COUNTIF($A$1:A1, $A$1:A1)<$B$1, A1, "")
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
How could you do this so it only counts the EXACT searchstring (so it would find AA but not AAB). I need to do this or something similar to count how many times certain numbers appear. The same number could appear in the same cell more than once. However, if i am searching for the number "1" i do not want it to also count "10" or "11".
This works! Thank you very much!!!
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.
Dear Oscar,
I have a string in a cell as follows:
MKS3PIN-5 DC24 with PF113A-E & PFC-A1. These are model numbers of a products.
I have a list containing these and much more. "With" is not a product.
MKS3PIN-5 DC24 is a single product even though there is a space before DC24.
I want to extract the model numbers only in adjacent columns and receive a message stating that all models have been extracted. Also to say which model number is not found in the string.
The main purpose of this exercise is to arrive at the combined prices of the above combination.
Thanks & Regards
S.Narasimhan
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/
Hi Oscar,
Clear explination! Thanks
If you replace the value in A1 to "AAA" then the answer should be 5
But the formula gives 4, because you substitute the found substring with nothing.
I read your suggestion on https://www.get-digital-help.com/2018/04/17/count-a-given-pattern-in-a-cell-value/
That is the right answer for 1 cell.
How do I use this formula for the search of a substring on the "tbl" from your example? Wich should give the answer 5........
Marcel Maatman
It is not possible to build an array with different number of rows and columns, like this:
1 1 1
2 3
1
This is needed for the formula to work. I need to think about this, not sure I can solve it. It can be done with a user defined function.
I want to use the above formula on filtered rows. How can I do that? Any help is appreciated. Thanks!
Dear Oscar,
This is a very helpful formula. My requirement is to use the exact formula but on filtered rows so the rows can change based on the filter condition. Is there an easy fix for this? Any pointers will be helpful. Thanks.
Paras Desai,
great question.
Array formula in cell C3:
=SUM((LEN(IF(SUBTOTAL(103, OFFSET(Table1[Country], MATCH(ROW(Table1[Country]), ROW(Table1[Country]))-1, 0, 1)), Table1[Text]))-LEN(SUBSTITUTE(IF(SUBTOTAL(103, OFFSET(Table1[Country], MATCH(ROW(Table1[Country]), ROW(Table1[Country]))-1, 0, 1)), Table1[Text]), $C$2, "")))/LEN($C$2))
Get the workbook
Count-string-in-a-filtered-table.xlsx
Paras Desai,
You also have the option to let the Excel defined Table do the math.
Formula in cell D5:
=(LEN([@Text])-LEN(SUBSTITUTE([@Text], $C$2, "")))/LEN($C$2)
1. Select any cell in the Excel defined Table.
2. Go to tab Table design.
3. Press with left mouse button on check box "Total row" to show the total.
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.