# 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, "")

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.

### 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)

=(SUM(LEN(tbl))-SUM(LEN(**SUBSTITUTE(A1:A6, $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(A1:A6))-**SUM(LEN(SUBSTITUTE(A1:A6, $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(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6, $B$9, ""))))/LEN($B$9)

SUM(LEN(A1:A6))

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(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

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; 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.

### 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)

becomes

LEN({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"})

and 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, "")

becomes

SUBSTITUTE({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"}, "AA", "")

and returns

{"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"}

#### Step 3 - Count characters in array

LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

becomes

LEN({"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"})

and returns

{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}

#### Step 4 - Subtract arrays

LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

becomes

{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}-{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}

and returns

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}

#### Step 5 - Divide with cell length

(LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2

becomes

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/LEN($E$2)

becomes

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}

and returns

{0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;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))

becomes

SUMPRODUCT({0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;0})

and 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.

**Contact Oscar**

You can contact me through this contact form

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.