Home > Formulas > Count > Specific Text Count Specific Text in Cell

How many times does a specific text string appear in a worksheet 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.
This formula is entered in cell C5, and copied down to cell C8:
Here's how the formula works
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.)
How many times does a specific text string appear, as a separate item, in a worksheet cell?
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:
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:
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"
Here's how the Excel 365 formula works:
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 commaseparated 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.
To create the XML code, our formula will replace the commas in commaseparated 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.
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.
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 commaseparated text in a cell:
You can learn more about XML (Extensible Markup Language) in this XML for the Uninitiated article on the Microsoft site.
For the XPath argument, cell H1 has a formula that combines text with our search word.
If dig is the search word, this will be the XPath:
In this example, here's what the XPath will do:
On the Items_XML sheet, this formula is in cell C5, to count the text items that match our search item:
Here's how the calculation in the formula works:
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 allinone formula in the next section.
NOTE: This example is on the Items_LEN sheet in the sample workbook.
The Excel formula refers to 2 named cells  SepSel and SepSel2. In the sample file, those named cells are on the Admin sheet.
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.
The formula combines the text value in cell B2, with the pipe character (SelSep2) at the start, and a comma (SelSep) at the end.
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:
Here is the formula from the helper column:
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,
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:
instead of
Here is the formula in the Count column:
Here's how the formula works:
If you don't want to create a helper column on your worksheet, you can get the same results with an allinone formula.
In the Count formula shown above, there are two references to the helper column (D5):
For the allinone formula, replace those refereces with this formula from the helper cell:
Here is the allinone formula, with the replacements made:
Count Criteria in Other Column
Count Cells With Specific Text
Last updated: April 13, 2024 11:50 AM