Sometimes we need to count text in Excel for many purposes. Different excel functions and formulas make this easy for us. In the article, we are going to learn how to count text in Excel with examples and explanations. Here’s an overview of the dataset, we’ve prepared for this article.
7 Quick Ways to Count Cells That Contain Text in Excel
1. Excel COUNTIF Function with Asterisk(*) to Count Text Cells
Excel COUNTIF function helps us to count the cell numbers with a given condition. Asterisk (*) is a wildcard character that represents any number of characters present in the cell. With the combination of these two, we can count text cells.
Here, we have a dataset (B4:B9) of sold items. The result will display in Cell D6.
STEPS:
- Select Cell D6 at first.
- Now write down the formula:
=COUNTIF(B5:B9,"*")- Then press Enter to see the result.
2. Count Specific Text in Excel
Most often we need to count the specific text in Excel. The COUNTIF function helps us in this case. Suppose we have a dataset (B4:B9) of sold items. We are going to find the total number of cells that contain the specific text “BKA” and return the result in Cell D7.
STEPS:
- In the beginning, select Cell D7.
- Next, type the formula:
=COUNTIF(B5:B9,"*BKA*")- Now hit Enter to see the result.
We use a wildcard character, Asterisk (*) just before and after the criteria text. This will make sure that all the cells containing the criteria text of the dataset are taken to count.
3. Excel Case Sensitive Text Count
In the below dataset (B4:B9), we have some case-sensitive issues. Like we can see that the item name “CKDA” also appeared in the “Ckda” form. We only need to count the cells that contain CKDA”. For that, we are going to use Excel case-sensitive function, the EXACT function along with the SUMPRODUCT function, and also add a double minus sign. This sign converts TRUE and FALSE values into numeric values 1’s and 0’s for easy calculation.
STEPS:
- First, select Cell D7.
- Write down the formula:
=SUMPRODUCT(--EXACT("CKDA",B5:B9))- Then press Enter to see the result.
4. Array Formula to Count Text in Excel
We use Excel Array Formula for doing one or more powerful calculations multiple times in a selected range. Let’s say we have a dataset (B4:B9) of sold items. It also has a blank cell. We are going to create an array formula here to count text.
STEPS:
- Select Cell D6.
- Now type the formula:
=SUM(IF(ISTEXT(B5:B9),1))- Then press CTRL + SHIFT + ENTER to see the result.
In the formula bar, we can see the array formula in curly brackets {}.
5. Combination of SUMPRODUCT and ISTEXT Functions to Count Text Cells
Excel ISTEXT function checks if the cell contains any text or not and returns TRUE or FALSE. The SUMPRODUCT function returns the sum of products by multiplying arrays together.
Assuming we have a dataset (B4:B9) of sold items. We can use the ISTEXT function wrapped into the SUMPRODUCT function to count text cells. We must add a double minus sign like the above procedure as it converts the TRUE and FALSE values into 1’s and 0’s so that the SUMPRODUCT function can easily calculate.
STEPS:
- First, select Cell D6.
- Then type the formula:
=SUMPRODUCT(--ISTEXT(B5:B9))- Finally, hit Enter to see the result.
6. Count Partial Text in Excel with COUNTIF Function
From the above procedures, we understand that the COUNTIF function helps us to count the cell numbers with a given condition. Excel functions and formulas make our calculations always easy. Like sometimes we don’t have to input the whole word in a function for searching. Suppose we have a dataset (B4:B9) of sold items. We want to find out the cells that contain “BKA” in the first portion.
STEPS:
- First of all, select Cell D6.
- After that, write down the formula:
=COUNTIF(B5:B9,"BKA*")- Finally, press Enter and see the result.
We use an Asterisk (*) in the criteria along with “BKA” as it helps the COUNTIF function to count all the cells containing BKA and everything in front of it.
7. Excel Count Text Cells Excluding Space Character
The COUNTIFS function helps us to count the cell numbers that meet the multiple criteria. We have a dataset (B4:B9) of items. It contains a space character in Cell B7. So we are going to count the text cells number excluding the space character with the help of the COUNTIFS function.
STEPS:
- Select Cell D6.
- Now type the formula:
=COUNTIFS(B5:B9,"*",B5:B9,"<> ")- Finally, press Enter, and the result is here.
How to Count Number of Characters of a Text in Excel
We can also find how many characters are lying in a text cell by using the LEN function in Excel. The LEN function returns the length of the given text cell. So we can use this function to count the total number of characters present in the text cell.
Assuming, we have a dataset (B4:B9) of sold items. We are going to count the characters of each text cell and return the result in range C4:C9.
STEPS:
- First, select Cell C5.
- Next type the formula:
=LEN(B5)- Now hit Enter.
- Use the Fill Handle tool to autofill the next cells And see the result.
Download Practice Workbook
Download the following workbook and exercise.
Conclusion
These are the easy ways to Count Text in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
