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

Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]

Question: How do I count the number of times a text string exists in a column? The text string may […]

### 23 Responses to “Count how many times a string exists in a cell range (case insensitive)”

### 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/2009/12/29/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/

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

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

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

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

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.