Home > Formulas > Count > Specific Text Count Specific Text in Cell Someone asked me how to count specific words within a cell. For example, count "air" on its own, but not as part of "hair". It was an interesting challenge, and you can see my solutions below.

A) 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.)

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

Excel Versions

If you have Excel 365, I recommend using the TEXTSPLIT formula.

-- B1) Excel 365 - TEXTSPLIT (recommended)

Note: For older versions of Excel, use one of the other formulas:

• -- B2) FILTERXML Formula (Excel 2013 or later)
• -- B3) Excel Formula - Helper Column
• -- B4) Excel Formula - All-in-One

B1) Excel 365 Formula - TEXTSPLIT

If you have Excel 365, use this TEXTSPLIT formula. It's much shorter and simpler than any of the other formula solutions.

The screen shot below is from the Excel 365 sample file. I entered the following formula in cell C5, and copied it down to cell C8:

• =SUM(--(TEXTSPLIT(SUBSTITUTE(B5, " ",""),",") =$B$2))

The formula results show the count of "air", as a separate word, in the text strings.

For example, in cell C7, the result is 2. It does not count the "air" in "hair"

How the Formula Works

Here's how the Excel 365 formula works:

• SUBSTITUTE replaces all of the space characters with an empty string:
  • SUBSTITUTE(B5," ","")
• TEXTSPLIT creates an array of items, split at the commas
• Each result is tested -- Is it equal to the value in cell B2? TRUE or FALSE
• The 2 minus signs (double unary) convert TRUE values to 1, and FALSE values to 0 (zero)
• SUM calculates the total of the 1 and 0 amounts

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

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

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

C) Download Sample Files

1. Excel 365 Formula: To see the sample data and Excel TEXTSPLIT formula from this page, download the Count Specific Cell Text with TEXTSPLIT 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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Function Tutorials

COUNT / COUNTIF

Count If Greater Than

Count Criteria in Other Column

Count Cells With Specific Text

Count Specific Items in List