5 Ways to Count Cells that Contain Text in Microsoft Excel (2024)

Do you need to count the number of cells in a Microsoft Excel spreadsheet that contain text? There are a few ways you can do it!

You might have a mixed set of text and numerical data and need to find out how much of it is text in order to get a better sense of your dataset.

In this blog post, you’ll see all the different methods for counting cells containing text data as well as the advantages and disadvantages of each method.

Get your copy of the example workbook with the above link and follow along to learn how to count cells that contain text in Microsoft Excel!

Count Cells that Contain Text using the COUNTIF Function

The first way to count your text data is using the COUNTIF function.

Usually, COUNTIF is used when you need to count data that meets a particular criterion. However, you can use it in a more generic way to count any text.

You can use the * wildcard character to represent any text within the COUNTIF criterion input.

= COUNTIF ( B3:B14, "*" )

Use the above formula to count all the text values in a range. The "*" condition will count anything in the range B3:B14 that is text.

The * character is a wildcard that can be used to represent any number of text characters when searching in text. For example, "A*" would represent any text that starts with A or "*A" would represent any text that ends with A.

Count Cells that Contain Text using the ISTEXT Function

Sometimes data that looks like a number has actually been entered as text. There are a few ways to determine if a cell really contains text data such as the ISTEXT function.

= ISTEXT ( B3 )

The above formula will check the contents of cell B3 and return TRUE if the cell contains text and FALSE if the cells contains a non-text value.

You can then use the ISTEXT function to count the number of text values in your data set.

= SUM ( --ISTEXT ( B3:B14 ) )

The above formula will return the total number of text values in the range B3:B14.

ISTEXT will test each cell in the range B3:B14 and return a TRUE value if it’s text and FALSE otherwise.

The double negative -- converts the array of boolean values to an array of 1‘s or 0‘s where 1 is TRUE or 0 is FALSE. When you take the SUM of this array of 1‘s and 0‘s you will get the count of text values!

Count Cells that Contain Text using Power Query

Excel comes with a tool called power query which allows you to clean and shape your data in almost unlimited ways and you can also use it to count the text values in your dataset.

Power query is best used with data inside Excel tables, so be sure to add your data to an Excel Table first.

1. Select any cell inside your data.
2. Go to the Data tab.
3. Press the From Table/Range command button.

This will open the power query editor where you can build your query to transform your data.

You will need to add a new temporary column to help filter out the non-text values before counting the results.

4. Go to the Add Column tab in the power query editor.
5. Select the Custom Column command.

This will open up the Custom Column menu where you will be able to define the formula for the new column.

= Text.Length([Values])

6. Add the above formula to the Custom column formula input.
7. Press the OK button.

The new column will return the length as a count of characters for each value. This will only work when the value is text. For any non-text values, this results in an Error.

You can then filter out the Error‘s and null values to get the count of text values.

8. Right-click on the custom column heading.
9. Select Remove Errors from the options.

Next, you can filter out any null values if there are any. These will be present if you have any blank cells in the source Excel data.

10. Left click on the filter toggle in the custom column heading.
11. Deselect the null value.
12. Press the OK button.

This will result in only the rows with text values remaining. These are the rows containing a number value in the Custom column.

You can now count the values in the Custom column.

13. Left click on the Custom column heading to select it.
14. Go to the Transform tab.
15. Click on the Statistics command.
16. Choose the Count Values option in the menu.

This will result in a single value shown in the data preview, and you can then use that value in other queries or load it into Excel.

17. Go to the Home tab.
18. Press the Close and Load button.

This single value will then load into a table in your Excel worksheet!

All the above steps should create the following M code behind the scenes.

// Text Countlet Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Length([Values])), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Custom] <> null)), #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[Custom])in #"Calculated Count"

The great thing about a power query is it gets saved as a query that can easily be run again when your data changes.

Go to the Data tab and press the Refresh button and the query will run again and give you the latest text count.

Count Cells that Contain Text using VBA

Visual Basic for Applications (VBA) is a programming language in Excel that can help you automate your work or add functionality to Excel.

This can be used to count text values in a selected range with the click of a button.

Follow these steps to create a VBA script that counts text values.

1. Press Alt + F11 to open the VBA editor. This is where you can write your VBA code.
2. Go to the Insert tab in the VBA editor.
3. Select Module from the menu.

Sub CountText()Dim txtCount As LongtxtCount = Application.WorksheetFunction.CountIf(Selection, "*")MsgBox "Found " & txtCount & " text value(s)"End Sub

4. Paste the above code into the VBA module.

The code actually uses COUNTIF worksheet function via the Application.WorksheetFunction VBA method.

You can now close the VBA editor and return the Excel workbook to use the macro script.

5. Select the range of cells in which you would like to count text.
6. Press Alt + F8 to open the Macro dialog box.
7. Select the CountText macro.
8. Press the Run button.

The code will run and return the count of text values in a small pop-up box.

Count Cells that Contain Text using Office Scripts

Excel also offers a feature called Office Scripts, which allows users to automate tasks by creating and running scripts. Office Scripts can be used to perform various tasks, such as updating cells, inserting new data, or deleting data.

Office Scripts is a JavaScript based scripting language that is available in Excel online with Microsoft 365 business plans. It’s meant to be the successor to the aging VBA language.

Using Office Scripts can help users to save time and increase productivity by automating repetitive tasks or creating new functionality within their spreadsheets.

1. Open your Excel file in Excel Online.
2. Go to the Automate tab.
3. Press the New Script command.

function main(workbook: ExcelScript.Workbook) { //Create a range object from selected range let selectedRange = workbook.getSelectedRange(); //Create an array with the data types in the selected range let selectedTypes = selectedRange.getValueTypes(); //Get dimensions of selected range let rowHeight = selectedRange.getRowCount(); let colWidth = selectedRange.getColumnCount(); //Set text counter let textCount = 0; // Loop through each item in the selected range for (let i = 0; i < rowHeight; i++) { for (let j = 0; j < colWidth; j++) { //Test if the value is text if (selectedTypes[i][j].toString() === "String") { //Increment the count textCount++; } } } //Write the count into the sheet just below the selected range selectedRange.getLastCell().getOffsetRange(2, 0).setValue(textCount);};

This will open the script editor where you can write your code.

4. Paste the above code into the editor.
5. Press the Save script button.

This code will loop through each cell in the selected range and test if the data type is a string.

The script will keep a running count of the number of cells that contain text and then place the final count into a cell just below the selection.

Now you will be able to count text data in any range!

6. Select the range of cells to count.
7. Press the Run button in the script editor.

The count will be calculated and added just below the selected range.

Conclusions

Getting an overview of your data is an essential step to understanding your data. Counting the text values in a column is a very common summary that will help you gain valuable insights.

There are several ways to count text values available in Excel.

The COUNTIFS function can be combined with wildcard characters to count any text in a range. Excel also has the ISTEXT function which can be combined with a SUM to count any text values.

You can also count text values with Power Query, VBA, or Office Scripts depending on the situation.

Do you know any other ways to count text data in Excel? Which method do you prefer? Let me know in the comments below!