How to count text in excel cell (3 Easy Methods)

August 1, 2022
1.1K Views
0

A free Office suite fully compatible with Microsoft Office

A free Office suite fully compatible with Microsoft Office

It's not just you. If you are unsure of which function to use at what time in Excel, counting cells with text might be a bit of a challenge.

Additionally, you may choose from a variety of formulas to assist you rapidly acquire the outcomes you want.

How to count text in cell using Text Strings:

It may be necessary to locate or count data based on their text content when working with a big data set that includes text strings. You might be aware of how to accomplish this with numerical numbers, but you might not be aware that you can also apply this to cells that hold text strings.

Imagine that you are looking for certain names in a list of names, but you are just looking for a portion of the name. You could wish to count names that contain the letter Ha, for instance.

Let's investigate our options. For this, we may utilise the COUNTIF() method.

This function's syntax is as follows:

= COUNTIF (range, criteria)

Range: the range of cells you wish to look in

Criteria: Is the text string or condition that we're looking for

To count names that include Ha, we may use =COUNTIF(A2:A20,*Ha*).

Asterisks (*) are required since they are wild cards that can match any number of characters, so take note of that.

You must keep in mind that this function ignores case. That implies that both *Ha* and *ha* would provide the same outcome.

Use the question mark (?) to denote one character when you wish to count the characters in a text string precisely. see illustration underneath

Text strings that include the letter si and have no more than five characters after it are counted using the syntax:

=COUNTIF(A2:A7,*si???)

Count text in cell using COUNTA:

Let's use the COUNTA function to determine how many cells in the table below's range of cells A1 to A5 are not empty.

The following is the Excel COUNTA formula:

=COUNTA(A1:A5)

The COUNTA function determines how many of the cells in A1 through A5 have some data in them. Since only cell A5 is vacant while cells A1, A2, A3, and A4 are not empty, it yields a value of 4. Cell B1 displays the outcome.

In the figure below, the COUNTA function's output is shown.

Counting empty range ( no text in cell) using COUNT:

Let's use the COUNT function on the empty range of values found in cells D3 to D5.

This is how the COUNT formula is written:

=COUNT(D3:D5)

The specified range is empty and devoid of any numbers. Therefore, the formula's output is 0, which is displayed in cell D10 (shown in the image below).

The COUNTA function determines how many of the cells in A1 through A5 have some data in them. Since only cell A5 is vacant while cells A1, A2, A3, and A4 are not empty, it yields a value of 4. Cell B1 displays the outcome.

In the figure below, the COUNTA function's output is shown.

Did you learn about how Excel count text in cell? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience