Count Specific Text in Cell

See how to count specific text within a single worksheet cell with Excel formulas, or with Google Sheets. Count all instances of a text string, or count whole words only.

Count Text String in Cell

How many times does a specific text string appear in a worksheet cell?

• In this example, the specific text string is entered in cell B2: air
• In cells B5:B8, there are cells with several text strings, separated by commas
  • NOTE: Some of the cells have extra spaces before/after the commas
• Formulas in column C count the number of times that "air" is in the cell

With this formula, the text string is counted whether it is a full word, or part of a word

NOTE: This formula (shown below) will work in both Excel, and in Google Sheets.

Formula to Count Specific Text String

This formula is entered in cell C5, and copied down to cell C8:

• =(LEN(B5) - LEN(SUBSTITUTE(B5, $B$2,""))) / LEN($B$2)

How It Works

Here's how the formula works

• LEN returns the length of the cell's text entry: LEN(B5)
• SUBSTITUTE replaces all instances of "air" with an empty string:
  • SUBSTITUTE(B5, $B$2,"")
• LEN measures that shortened text
• The new length (14) is subtracted from the old length (23)
• The difference (9) is divided by the length of the specific text string (3)
• The result of that division is the count of instances (3)

Here's the formula, with each section calculated, in the Excel formula bar. (Select parts of the formula, then press the F9 key, to calculate that section.)

Count Specific Text Items in Cell

How many times does a specific text string appear, as a separate item, in a worksheet cell?

• In this example, the specific text string is entered in cell B2: air
• In cells B5:B8, there are cells with several text strings, separated by commas
  • NOTE: Some of the cells have extra spaces before/after the commas

Solutions for Excel and Google Sheets

For this problem, there are different solutions in Google Sheets, and in Excel.

With these formulas, the text string is counted only if it is on its own between commas (with or without space characters).

-- Google Sheets Formula

-- Excel 365 Formula

-- Excel Formula - Helper Column

-- Excel Formula - All-in-One

Google Sheets Formula

The Google Sheets formula is much shorter than the Excel formula, thanks to the SPLIT function:

This formula is entered in cell C5, and copied down to cell C8:

• =COUNTIF(SPLIT(SUBSTITUTE(B5," ",""),","),$B$2)

How It Works

Here's how the Google Sheets formula works

• SUBSTITUTE replaces all of the space characters with an empty string:
  • SUBSTITUTE(B5," ","")
• SPLIT creates an array of items, split at the commas
• COUNTIF counts all the items in that array, if they match the text in cell B2

Here's what the SPLIT function does if it's the only function in the formula.

• The results fill across the columns to the right.
• The COUNTIF function could count the matching items in that range of cells.

By combining SPLIT and COUNTIF, the results are all in one cell.

Excel Formulas

Unfortunately, Excel does not have a SPLIT function, so a longer formula is needed.

-- If you have Excel 365, use formula with SEQUENCE function

-- If you have Excel 2013 or later, use formula with FILTERXML function

-- In earlier versions of Excel

---- Use the formula with a helper column

---- OR, use the all-in-one formula, if you prefer

Excel Formula - SEQUENCE

If you have Excel 365, use this formula which includes the SEQUENCE function. Thanks to UniMord for sending this formula!

The formula also uses the SUM and SUBSTITUTE functions, and here is the formula in cell C5. I've added a few space characters, for readability:

• =SUM(--(SUBSTITUTE(","& SUBSTITUTE(B5," ","") & ",", "," & $B$2&",","", SEQUENCE(LEN(B5))) <> "," & SUBSTITUTE(B5," ","")&","))

How It Works

I've colour coded parts of the formula, and broken it onto several lines. There are notes on each section below.

Formula Overview

For this formula explanation, I'll use the text string from cell B5:

• air,dig,air,air

Here's an overview of what the formula does:

1. The bright blue SUBSTITUTE function creates Text String 1.
2. That is compared to Text String 2, which is created by the purple part of the formula.
   • If the two text strings are not equal, the result is TRUE
   • If they are equal, the result is FALSE
3. The 2 minus signs inside the first bracket convert those results to numbers
   • TRUE = 1
   • FALSE = 0
4. The SUM function adds those numbers, to get the count of how many times the specific word is found in the cell, as a separate item.

Bright Blue SUBSTITUTE

The bright blue SUBSTITUTE uses 4 arguments to create Text String 1:

• Text, Old Text, New Text, Instance Num

Text Argument

The red part of the formula joins 3 pieces of text:

1. a comma
2. text from cell B5, with spaces replaced by empty strings
3. another comma

The result is this text string: ,air,dig,air,air,

Old Text Argument

The dark blue part of the formula joins 3 pieces of text:

1. a comma
2. search word from cell B2
3. another comma

The result is this text string: ,air,

New Text Argument

The black part of the formula is an empty string: ""

Instance Num Argument

The green part of the formula creates a list of numbers:

• starting at 1
• ending at the number of characters in cell B5 -- LEN(B5)

Because SEQUENCE is a spill function, it returns multiple numbers, instead of just 1 number

There are 17 characters in cell B5, so here is the result for the Instance Num argument:

1,2,3,4,5,6,7,8,9.10,11,12,13,14,15,16,17

Bright Blue SUBSTITUTE

Because SEQUENCE is a spill function, the bright blue SUBSTITUTE function returns 17 text strings. There is one result for each sequence number.

Here are the first 6 instances, if they were listed on a worksheet

1. For instance 1, the first ,air, is removed, and the result is dig,air,air,
2. For instance 2, the second ,air, is removed, and the result is ,air,digair,
3. For instance 3, the third ,air, is removed, and the result is ,air,dig,air
4. For instance 4, there is no fourth instance, so the result is ,air,dig,air,air,

Comparison Text String 2

The purple part of the formula creates a comparison text string - Text String 2

It is exactly the same as the red part of the formula, and joins 3 pieces of text:

1. a comma
2. text from cell B5, with spaces replaced by empty strings
3. another comma

The result is this text string: ,air,dig,air,air,

Compare Text Strings

Next, for each instance number, Text String 1 is compared to Text String 2, using the NOT EQUAL operator - <>

• If the two text strings are not equal, the result is TRUE
• If they are equal, the result is FALSE

SUM the Results

Next, the 2 minus signs inside the first bracket convert those TRUE/FALSE results to numbers

• TRUE = 1
• FALSE = 0

And finally, the SUM function adds those numbers, to get the count of how many times the specific word is found in the cell, as a separate item.

FILTERXML Formula

If you have Excel 2013, use this FILTERXML formula. It's not available in online Excel or Excel for Mac.

FILTERXML returns specific data from XML content, based on a specified XPath. Our formula will return specific items from comma-separated text, based on our search word.

You can learn more about FILTERXML on the Microsoft site.

NOTE: It was an interesting journey, trying to figure out the FILTERXML solution, and I wrote about that on my Contextures blog. Scroll down to the section on Experiments with FILTERXML XPath, if you're interested in the gory details.

Create the XML

To create the XML code, our formula will replace the commas in comma-separated text. For example, this string of text is in a cell:

house, dig, air , hair , air, dig

The formula will change it to structures XML, like this.

• d represents the cell data
• i lines are the comma-separated items
• n is an attribute of 1 for each item, so they can be summed later

This XML technique is adapted from a comment on Chandoo's forum, and you can learn more about XML (Extensible Markup Language) in this XML for the Uninitiated article on the Microsoft site.

XML Strings

To make it easier to create each XML string, there are 4 cells with strings at the top of the worksheet.

Our FILTERXML formula combines those values with the comma-separated text in a cell:

1. Text in E1 goes before the cell text
2. Text in F1 replaces the commas
3. Text in G1 goes after the cell text

You can learn more about XML (Extensible Markup Language) in this XML for the Uninitiated article on the Microsoft site.

Create XPath

For the XPath argument, cell H1 has a formula that combines text with our search word.

• ="//i[normalize-space()='" & B2 & "']/@n"

If dig is the search word, this will be the XPath:

• //i[normalize-space()='dig']/@n

What It Means

In this example, here's what the XPath will do:

• return the n attribute (1) for all items that are equal to dig.
• normalize-space function removes leading and trailing spaces, and extra spaces between words, like Excel's TRIM function.

FILTERXML Formula

On the Items_XML sheet, this formula is in cell C5, to count the text items that match our search item:

• =IFERROR(
     SUM(
     FILTERXML($E$1& SUBSTITUTE(B5,",",$F$1) &$G$1,$H$1))
     ,0)

How It Works

Here's how the calculation in the formula works:

• To create XML code,
  • SUBSTITUTE replaces all the commas with the text string in cell F1:
    • SUBSTITUTE(B5,",",$F$1)
  • Text string from E1 is joined at the start
  • Text string from G1 is joined at the end
    • $E$1&SUBSTITUTE(B5,",",$F$1)&$G$1
• FILTERTEXT returns the number attribute (1) from that XML code, based on the XPath in cell H1
• SUM returns a total of all the 1s
• IFERROR returns a zero, if no matching items are found

Excel Formula Helper Column

If you don't have Excel 2013 or later, this formula will work in earlier versions. This example uses a helper column, and there is an all-in-one formula in the next section.

NOTE: This example is on the Items_LEN sheet in the sample workbook.

Separators

The Excel formula refers to 2 named cells - SepSel and SepSel2. In the sample file, those named cells are on the Admin sheet.

Search String

To identify each separate item in the text cells, those separators are used to create a search string. In the sample file, this formula is in cell D2, on the CountItems sheet.

• =SelSep2 &$B$2&SelSep

The formula combines the text value in cell B2, with the pipe character (SelSep2) at the start, and a comma (SelSep) at the end.

Helper Column Formula

The Excel formula could be entered all in one cell, but a helper column will make it easier to understand.

In the helper column, the formula will create a text string that marks each item:

• replace all space characters with empty strings, using SUBSTITUTE
• put the pipe character (SelSep2) at the start
• replace all commas (SelSep) with a comma and pipe character, using SUBSTITUTE
• put a comma at the end

Here is the formula from the helper column:

• =SelSep2 &
  SUBSTITUTE(
      SUBSTITUTE(B5," ",""),
  SelSep,SelSep & SelSep2)
  & SelSep

And here are the results in the helper column. It will be easier to find and count the items that match the search string in the grey call -- |air,

Count Text Items Formula

The final formula will use that helper column, to count the text items. This formula is like the first one on this page, that counted all occurrences of a text string. But in this formula, we'll refer to:

• the helper column, and the search string formula

instead of

• the text cell and the item to count.

Here is the formula in the Count column:

• =(LEN(D5) -LEN(SUBSTITUTE(D5,$D$2,""))) / LEN($D$2)

How It Works

Here's how the formula works:

• LEN returns the length of the helper column text: LEN(D5)
• SUBSTITUTE replaces all instances of "|air," with an empty string:
  • SUBSTITUTE(D5, $D$2,"")
• LEN measures that shortened text
• The new length (5) is subtracted from the old length (20)
• The difference (15) is divided by the length of the specific text string (5)
• The result of that division is the count of instances (3)

Excel All-in-One Formula

If you don't want to create a helper column on your worksheet, you can get the same results with an all-in-one formula.

In the Count formula shown above, there are two references to the helper column (D5):

• =(LEN(D5) -LEN(SUBSTITUTE(D5,$D$2,""))) / LEN($D$2)

For the all-in-one formula, replace those refereces with this formula from the helper cell:

• =SelSep2& SUBSTITUTE(SUBSTITUTE(B5," ",""), SelSep,SelSep & SelSep2) & SelSep

Here is the all-in-one formula, with the replacements made:

• =(LEN(SelSep2 & SUBSTITUTE(SUBSTITUTE(B5," ",""), SelSep,SelSep & SelSep2) & SelSep)
  -LEN(SUBSTITUTE(SelSep2 & SUBSTITUTE(SUBSTITUTE(B5," ",""),SelSep,SelSep & SelSep2) & SelSep,$D$2,""))) / LEN($D$2)

Download Sample File

1. Excel 365 Formula: To see the sample data and Excel SEQUENCE formula from this page, download the Count Specific Cell Text with SEQUENCE workbook. The zipped file is in xlsx format, and does not contain any macros.
2. Excel Formulas: To see the sample data and formulas for earlier versions of Excel, download the Count Specific Cell Text workbook. The zipped file is in xlsx format, and does not contain any macros.
3. Google Sheets: To see the sample data and Google Sheets formulas from this page, go to the Count Specific Items sample Google Sheets workbook. That file is VIEW ONLY. To create a copy that you can edit, click the File menu, then click Make a Copy.

More Function Tutorials

COUNT / COUNTIF

Count Criteria in Other Column

Count Cells With Specific Text

Count Specific Items in List