How to Count Text in Excel (7 Easy Tricks)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to count text in excel


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.

Use COUNTIF Function with Asterisk

STEPS:

  • Select Cell D6 at first.
  • Now write down the formula:
=COUNTIF(B5:B9,"*")
  • Then press Enter to see the result.

Use COUNTIF Function with Asterisk to Count Text in Excel


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.

Count Specific Text

STEPS:

  • In the beginning, select Cell D7.
  • Next, type the formula:
=COUNTIF(B5:B9,"*BKA*")
  • Now hit Enter to see the result.

Count Specific Text in Excel

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.

Count Case Sensitive Text

STEPS:

  • First, select Cell D7.
  • Write down the formula:
=SUMPRODUCT(--EXACT("CKDA",B5:B9))
  • Then press Enter to see the result.

Count Case Sensitive Text in Excel


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.

Apply Array Formula

STEPS:

  • Select Cell D6.
  • Now type the formula:
=SUM(IF(ISTEXT(B5:B9),1))
  • Then press CTRL + SHIFT + ENTER to see the result.

Apply Array Formula

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.

Use SUMPRODUCT and ISTEXT Function

STEPS:

  • First, select Cell D6.
  • Then type the formula:
=SUMPRODUCT(--ISTEXT(B5:B9))
  • Finally, hit Enter to see the result.

Use SUMPRODUCT and ISTEXT Function to Count Text in Excel


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.

Use COUNTIF Function to Count Text in Excel

STEPS:

  • First of all, select Cell D6.
  • After that, write down the formula:
=COUNTIF(B5:B9,"BKA*")
  • Finally, press Enter and see the result.

Use COUNTIF Function to Count Text in Excel

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.

Use COUNTIFS Function to Count Text in Excel

STEPS:

  • Select Cell D6.
  • Now type the formula:
=COUNTIFS(B5:B9,"*",B5:B9,"<> ")
  • Finally, press Enter, and the result is here.

Use COUNTIFS Function


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.

Count Number of Text Character in a Cell

STEPS:

  • First, select Cell C5.
  • Next type the formula:
=LEN(B5)
  • Now hit Enter.

Use LEN Function to Count Number of Text Character in a Cell in Excel

  • Use the Fill Handle tool to autofill the next cells And see the result.

Use LEN Formula to Count Number of Text Character in a Cell in Excel


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.


Related Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo