Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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.

Count Characters in Excel

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?

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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

=LEN(TRIM(A2)

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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(

Count Characters in Excel

STEP 2: Enter the LEN function.

=SUMPRODUCT(LEN(

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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

=SUMPRODUCT(LEN(A2:A6))

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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

Count Characters in Excel

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.

Count Characters in Excel

Follow the steps below to achieve the result –

STEP 1: Enter the LEN function.

=LEN(

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 2: Select the cell containing the text.

=LEN(A2)

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 3: Enter the minus (-) sign.

=LEN(A2)-

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 4: Enter the LEN function.

=LEN(A2)-LEN(

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 5: Enter the SUBSTITUTE function.

=LEN(A2)-LEN(SUBSTITUTE(

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 6: Select the cell containing the text.

=LEN(A2)-LEN(SUBSTITUTE(A2

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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

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

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

STEP 8: Type an empty string.

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

How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline

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

Count Characters in Excel

 

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.

If you like this Excel tip, please share it
How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline How to Count Characters in Excel - 3 Easy Formulas | MyExcelOnline
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  New Formulas in Excel 2019: CONCAT, IFS, MAXIFS, MINIFS, SWITCH and TEXTJOIN!

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...