Excel Count Cells with Text (Table of Contents)
Overview of Excel Count Cells with Text
When we work with data, data contains both qualitative and quantitative data as well. To store data excel is one of the platforms. Often times when the data is in the mixture of both numerical and non-numerical data at times we may require to count the number of cells which contain only numerical values as well we need the count of non-numerical value cells i.e. cells that contains only text values.
In excel we have formulas to deal with this kind of scenario. In our one of the earlier article “COUNT Formula in Excel,” we have explained about “COUNT” function in excel to count the number of cells which contains the numerical values in the supplied range of cells. In this article, we will show you the formula to count the number of cells that contain the non-numerical cells in excel.
Count Only Cells Which has Text Values
In excel we have “COUNT” function to count numerical value cells and “COUNTA” function to count non-blank cells irrespective of the data it has.
Is there any formula in excel which counts only text values??
Unfortunately, there is no built-in function in excel to count text value cells unlike formula for numerical values.
How do we Count then?
We need to make use of the COUTNIF function with wildcard characters with it. Before I tell you about how to count text value cells let me brief you about text value forms in excel.
4.8 (1,388 ratings)
Type 1: Text value can come in the form of Text Values and Alphanumeric Text Values. For example “Excel” is text value and “Excel 2019” is an alphanumeric value.
Type 2: Text value could be in the form of an empty string as well. For example, the values which are not visible to the eyes but contains some kind of text in it, those values are “Space Character” and “Apostrophe” character.
Type 3: Text value is also part of logical values i.e. TRUE or FALSE.
Type 4: All the special characters are considered as text values.
Wildcard Characters to Count Text Values
In order to count text values with the COUNTIF function, we need to use wildcard characters. Below are the wildcard characters we use with COUTNIF function.
Asterisk (*): Using this wildcard character we can match any number of characters in a cell. For example “Go*” can match “Good”, “Going”, “Government”, “Gold”.
Question Mark (?): This is used to match any single character from the supplied cell value. For example “A?ide” can match “Aside”, “Abide”.
Tilde (∼): This is used to match wildcard characters in a string.
Examples of Excel Count Cells with Text
Here are some examples of count cell with text in excel with the steps and the calculation.
Example #1 – Counting the Text Values
For this example, I have created a simple data set which includes, numbers, text values, special characters, logical values, and empty string values. Below is the image of the same data.
From cell A1 to A11 we have values. In cell A8 we have space character and in cell A10 we have an apostrophe.
Let’s count all the text values.
Step 1: Open COUNTIF function in one of the cells.
Step 2: Select the range as A1 to A11.
Step 3: Now in the criteria argument we need to specify what we need to count from the selected range of cells. Since we need to count all the text values supply “Asterisk (*)” as the criteria.
Hit enter key to have a total number of cells which contains text values.
So, the total number of cells contains the text values are 7. Except for the cells A3, A5, A6, and A11 we have text values in the remaining 7 cells.
Example #2 – Count of the Formula
Now I have added two more values to the list as “TRUE” & “FALSE”.
Now in the formula cell, I will just change the range from A1:A11 to A1:A13 and see what would be the count of the formula.
Even though we have extended our range selection formula still showing 7 as the text values count, the reason behind this is because “excel does not treat logical values TRUE or FALSE” as the text values, so it will be treated as 1 and 0 respectively.
Example #3 – Count Text Values Except for Blank Cell
In the above example, we have counted blank cells as text value cells. So this example shows how to count text values except for blank cells.
In the criteria argument, I have supplied the criteria as “?*”.
This evaluates the cells as at least one character should be there in the cell to count as a text value. So, an empty cell cannot be counted with these criteria.
This is a guide to Excel Count Cells with Text. Here we discuss Excel Count Cells with Text along with examples and downloadable excel template. You may also look at the following articles to learn more –