Count Text in Excel Cell

Introduction to Counting Text in Excel Cells

When working with Excel, it’s often necessary to count the number of cells that contain specific text. This can be useful for a variety of tasks, such as data analysis, reporting, and dashboard creation. Fortunately, Excel provides several ways to count text in cells, including using formulas, functions, and conditional formatting.

Using the COUNTIF Function

One of the most common ways to count text in Excel cells is by using the COUNTIF function. This function allows you to count the number of cells in a range that meet a specific criteria, such as containing a certain word or phrase. The syntax for the COUNTIF function is: COUNTIF(range, criteria) Where range is the range of cells you want to count, and criteria is the text you’re looking for.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=COUNTIF(A1:A10, "*example*")

This formula will return the number of cells in the range that contain the word "example", regardless of its position in the cell.

Using the COUNTIFS Function

If you need to count cells based on multiple criteria, you can use the COUNTIFS function. This function allows you to specify multiple ranges and criteria, and returns the number of cells that meet all of the criteria. The syntax for the COUNTIFS function is: COUNTIFS(range1, criteria1, [range2], [criteria2], …) Where range1 and criteria1 are the first range and criteria, and range2 and criteria2 are the second range and criteria, and so on.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example" and also contain the word "test", you would use the following formula:

=COUNTIFS(A1:A10, "*example*", A1:A10, "*test*")

This formula will return the number of cells in the range that contain both the word "example" and the word "test".

Using the LEN Function

Another way to count text in Excel cells is by using the LEN function. This function returns the length of a text string, which can be useful for counting the number of characters in a cell. The syntax for the LEN function is: LEN(text) Where text is the text string you want to count.

For example, if you want to count the number of characters in the cell A1, you would use the following formula:

=LEN(A1)

This formula will return the number of characters in the cell A1.

Using the FILTER Function

If you’re using Excel 365 or later, you can use the FILTER function to count text in cells. This function allows you to filter a range of cells based on a specific criteria, and returns the filtered range. The syntax for the FILTER function is: FILTER(range, include) Where range is the range of cells you want to filter, and include is the criteria you want to apply.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=FILTER(A1:A10, ISNUMBER(SEARCH("example", A1:A10)))

This formula will return the range of cells that contain the word "example". You can then use the COUNTA function to count the number of cells in the filtered range:

=COUNTA(FILTER(A1:A10, ISNUMBER(SEARCH("example", A1:A10))))

Notes

📝 Note: When using the COUNTIF and COUNTIFS functions, make sure to enclose the criteria in double quotes, and use the asterisk (*) wildcard to match any characters before or after the criteria.

📝 Note: When using the LEN function, make sure to enter the text string correctly, as the function is case-sensitive.

📝 Note: When using the FILTER function, make sure to use the ISNUMBER and SEARCH functions correctly, as the function is case-sensitive.

Example Table

Cell	Text	Count
A1	example	=COUNTIF(A1:A10, “example”)
A2	test example	=COUNTIFS(A1:A10, “example”, A1:A10, “test”)
A3	example text	=LEN(A3)
A4	example	=FILTER(A1:A10, ISNUMBER(SEARCH(“example”, A1:A10)))

In summary, counting text in Excel cells can be done using various formulas and functions, including COUNTIF, COUNTIFS, LEN, and FILTER. By using these functions, you can easily count the number of cells that contain specific text, and perform a variety of data analysis and reporting tasks.