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. 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. 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. 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. 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. This will open up the Custom Column menu where you will be able to define the formula for the new column. 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. 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. 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. 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. 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. 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. 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. 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. This will open the script editor where you can write your code. 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! 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!
