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.
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?
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).
-- Excel Formula - Helper Column
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:
Here's how the Google Sheets formula works
Here's what the SPLIT function does if it's the only function in the formula.
By combining SPLIT and COUNTIF, the results are all in one cell.
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
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:
I've colour coded parts of the formula, and broken it onto several lines. There are notes on each section below.
For this formula explanation, I'll use the text string from cell B5:
Here's an overview of what the formula does:
The bright blue SUBSTITUTE uses 4 arguments to create Text String 1:
Text Argument
The red part of the formula joins 3 pieces of text:
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:
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:
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
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
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:
The result is this text string: ,air,dig,air,air,
Next, for each instance number, Text String 1 is compared to Text String 2, using the NOT EQUAL operator - <>
Next, the 2 minus signs inside the first bracket convert those TRUE/FALSE results to numbers
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.
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.
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.
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 comma-separated 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 all-in-one 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 all-in-one formula.
In the Count formula shown above, there are two references to the helper column (D5):
For the all-in-one formula, replace those refereces with this formula from the helper cell:
Here is the all-in-one formula, with the replacements made:
Count Criteria in Other Column
Count Cells With Specific Text
Last updated: January 5, 2021 3:42 PM
Contextures RSS Feed