How to Count Characters in Excel – 3 Easy Formulas

Excel provides several built-in functions that make it easy to count characters for different purposes. It can be used to determine the number of individual characters (letters, numbers, symbols, and spaces) within a cell, a range of cells, or specific characters within the text in Excel. In this article, we will cover the following topics in detail –

Let us look at each of these methods.

Download the Excel Workbook below to follow along and understand How to Count Characters in Excel –
download excel workbookCount-Characters-in-Excel.xlsx

 

Count Characters in Cell

The LEN function is a straightforward and user-friendly formula in Excel. It gives you the count of the total number of characters in a single cell. It includes all characters in the cell, whether they are spaces, punctuation marks, special characters, numbers, and more. The syntax of this function is –

=LEN(text)

where,

Text – The text whose length you want to find.

In the following example, let’s determine the number of characters in cell A2. To get the result, enter the LEN formula followed by the reference to cell A2 enclosed in parentheses.

=LEN(A2)

Once you execute the formula, Excel will display the total number of characters present in cell A2.

In the example below, you will see that there are additional spaces within the text. What if you want to remove those unwanted spaces and then get the count of characters?

You can seamlessly achieve this by combining the LEN function with TRIM. The modified formula would look like this –

=LEN(TRIM(A2)

By incorporating TRIM into the equation, unwanted spaces are first removed, and subsequently, the LEN function provides you with an accurate count of characters, excluding any unwanted spaces.

 

Count Characters in Range of Cells

When you need to analyze multiple cells at once, you can use the SUMPRODUCT and LEN functions together. LEN will provide you with the count of characters in each cell and SUMPRODUCT will provide you with the summation of those values.

In this example, our goal is to determine the cumulative number of characters in the range A2:A6. Follow the steps outlined below to obtain the desired result –

STEP 1: Enter the SUMPRODUCT function.

=SUMPRODUCT(

STEP 2: Enter the LEN function.

=SUMPRODUCT(LEN(

STEP 3: Select the range of cells containing the text whose length you want to find.

=SUMPRODUCT(LEN(A2:A6))

Once you execute the formula, Excel will display the total number of characters in the cell range A2:A6.

If you manually count the characters in each cell and then sum up these individual counts, the result would be the same as the one provided by the SUMPRODUCT-LEN combination.

 

Count Specific Characters

Counting occurrences of a specific character within a cell can be accomplished using a combination of the LEN and SUBSTITUTE functions. The SUBSTITUTE formula in Excel can help you replace one specific text with another easily.

You can use this function to replace the instances of a specified character (that you want to count) with an empty string, allowing you to calculate the difference in length before and after substitution.

=LEN(text)-LEN(SUBSTITUTE(text,old_text,new_text)

In this example, you need to count the occurrence of the letter “a” in the text mentioned in cell A2.

Follow the steps below to achieve the result –

STEP 1: Enter the LEN function.

=LEN(

STEP 2: Select the cell containing the text.

=LEN(A2)

STEP 3: Enter the minus (-) sign.

=LEN(A2)-

STEP 4: Enter the LEN function.

=LEN(A2)-LEN(

STEP 5: Enter the SUBSTITUTE function.

=LEN(A2)-LEN(SUBSTITUTE(

STEP 6: Select the cell containing the text.

=LEN(A2)-LEN(SUBSTITUTE(A2

STEP 7: Type the character that you want to count within quotes.

=LEN(A2)-LEN(SUBSTITUTE(A2,”a”

STEP 8: Type an empty string.

=LEN(A2)-LEN(SUBSTITUTE(A2,”a”,””)

Excel will provide you with the count of the letter “a” in the text mentioned in cell A2.

 

Conclusion

Whether you’re working with large datasets or simple tables, understanding how to count characters in Excel can save time and enhance your data analysis skills. In this article, we have covered three distinct methods – counting characters in a cell, counting characters in a range of cells using SUMPRODUCT and LEN functions, and counting specific characters using LEN and SUBSTITUTE functions.

Learn more about Formulas with our 101 Advanced Excel Formulas & Functions Examples.