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.

**Table of Contents**hide

**Practice Workbook**

Download the following workbook and exercise.

## 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 {}.

**Similar Readings:**

**VBA for Each Cell in Range in Excel (3 Methods)****How to Use VBA Range Offset (11 Ways)****Use the Range Object of VBA in Excel (5 Properties)**

### 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 calculation 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:**

- Select
**Cell D6**at first. - 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.

## 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.

## 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.