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.
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?
Download excel example file
count-text-string-in-a-column.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Len(text)
Returns the number of characters in a text string
Substitute(text, old_text, new_text, [instance_num])
Replaces existing text with new text in a text string
Sum(number1,[number2],)
Adds all the numbers in a range of cells
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
Read more related articles in the archive.
Related articles
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 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 [โฆ]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in [โฆ]
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 [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two [โฆ]
11 Responses to โCount text string in a range (case sensitive)โ
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.
English
Spanish
){kind=link}
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.
When I use this formula. it does not work
Don,
Did you create an array formula?
I have added new instructions to this post.
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.