How to Count Text in Excel

When working with text values in Excel, you may come across a situation where you want to know how many times a particular text appears in a column. If the list consists of a few entries, manually counting them would be the easiest option. But, for a large dataset, it would be very inefficient and time-consuming.

For such cases, Excel has a built-in function called COUNTIF which allows you to count cells by specifying one or more conditions. Along with it, you can also find several other functions like LEN and COUNTA to count cells with texts in different scenarios.

Using COUNTIF Function

The COUNTIF function simply counts cells based on specific criteria. We can use it to count cells that contain only text values.

Main Formula Syntax:
=COUNTIF(cell range, criteria)

Where,

• cell range: cell or cell reference containing text values
• criteria: condition to look for particular kind of text

Count All Cells With Text

If you want to count only the text values, use the COUNTIF function as follows:

Syntax:
=COUNTIF(cell range, "*")

Here, using a wildcard character like * tells Excel to search only for text values, which can be of any length. To count texts which are at least one character long, replace * with ?* in the above syntax.

For instance,

In the above image, the above formula returned the total number of cells containing only the text values in the cell range B4:B10.

As you can see, the above formula doesn’t count any number/date values and blank cells.

Count Cells With Specific Text

Syntax:
=COUNTIF(A:A,,"jack")

The above formula counts cells containing the exact text value jack.  However, you can replace jack with another preferred text.

For instance,

Here, the formula only counted cells containing exactly the specified text “jack” (A2 and A3 in this case). But, since the COUNTIF function isn’t case-sensitive so it counted “Jack” as well.

Furthermore, you can add a wildcard character like * to count cells containing a specific text. It has a similar syntax but with an additional wildcard character.

Syntax:
=COUNTIF(A:A, *jack*) , where* is a wildcard character.

With this syntax, you can count any text that includes a particular string, such as jack. Additionally, it counts for any partial match as well. 

For instance, it counts the text “Jackie” too because “jack” appears in it.

One important thing to remember is that the COUNTIF function isn’t case-sensitive in this case as well. Meaning, it counts both “jack” and “JACK”. So, it will also count any duplicate texts.

For instance,

In the above image, the formula is =COUNTIF($E$3:$E$8,CONCAT("*",G6,"*")).

Here, we are searching for the cell value of the G6 cell (jack) in the Names column using the wildcard character *.

Count Cells that Begin with Specific Text

Syntax:
=COUNTIF(A:A,"jack*")

With the above syntax, you can count cells that begin that specific text such as “jack”.

For instance,

Count Cells that End with Specific Text

Syntax:
=COUNTIF(A:A,"*jack")

With the above syntax, you can count cells that end with specific text such as “jack”.

For instance,

Using SUMPRODUCT Function

Another great alternative to COUNTIF is using the SUMPRODUCT function along with the ISTEXT function. It’s specifically useful to count cells that contain only the text values.

The ISTEXT function returns a TRUE/FALSE value depending on whether the cell contains a text value.

Likewise, the SUMPRODUCT function counts the total number of values “1″, which is the number equivalent to TRUE.

Syntax:
=SUMPRODUCT(--ISTEXT(cell range))

The SUMPRODUCT function only accepts and counts 0 or 1. So, we have inserted the double-minus sign before the ISTEXT function to convert the boolean value TRUE/FALSE to the equivalent number 1 or 0.

For instance,

Using COUNTA Function

The COUNTA function counts all the cells containing any kind of text and returns the total number of non-blank cells. However, it still counts those with blank spaces.

Syntax:
=COUNTA(range)

For instance, 

Here, cell C3 is blank, so the formula excludes it while counting the cells. 

Now, to count cells that contain only the text values, you can use the COUNTA function in combination with the COUNT function. 

For instance,

In the above image, the COUNTA function counts all the non-empty cells (6 in this case). And, the COUNT function returns the number of cells containing the number and date values (3 in this case).

So, the whole =COUNTA(A2:A8)-COUNT(A2:A8) formula evaluates to 6-3 which equals 3 as the final result.

Using LEN Function

The LEN() function counts the total number of characters in a text. Such texts can include symbols, numbers, and spaces as well.

You can use it to count text on a single cell or for each cell in a cell range.

Syntax:
=LEN(text)

Here, you can specify an individual cell or a cell range to get the length of text for each of them.

For instance,

Here, we have used the formula =LEN(A3:A7) which counts each text in the Name column.

Note that the above function also counts the spaces between characters. To count them without spaces, use the SUBSTITUTE function along with the LEN function as follows.

Syntax:
=LEN(SUBSTITUTE(cell range," ",""))

For instance,

In the above image, the SUBSTITUTE first replaces any space characters in the text for each cell. Then, the LEN function counts the characters without spaces.

Using VBA Code

If you aren’t a fan of using formulas, VBA could be an appropriate option. Here, you just need to execute the VBA code. Then, it will automatically return you the total number of cells with text, excluding any numbers, dates, and blank cells.

1. Select the cells where you want to count texts.
2. Right-click on the active worksheet and select View Code.
   
3. Paste the following into the main window.
   

Sub CountTextValues()

Dim countText As Long
countText = Application.WorksheetFunction.CountIf(Selection, "*")

MsgBox "You have" & " " & countText & " text value (s) in the selected cell range."

End Sub

4. Click the Run icon. Or, press the F5 button.
   
5. To save the worksheet with the code, choose the XLSM format next to Save as type field.