How To Count Characters In Excel

How To Count Characters In Excel

When you need to count characters in excel, the LEN function is what you want to use. The function counts the number of letters, numbers, characters, and spaces in a string. As an example, the length of the sentence “Excel is a very useful tool, and it is simple to analyse data” (without the quotes) is 51 characters.

The LEN function in Excel count characters in Excel

LEN Function

To count the number of characters in a range of cells, use the SUM and LEN functions

SUM+LEN Function

You can also use Array formula to count number of characters in given range of cells

Array Formula

It is important to note that you may end an array formula by hitting CTRL + SHIFT + ENTER. Excel automatically inserts the curly brackets. The SUM function is used with the array constants 9;4;6;5 as a parameter, yielding a result of 24 as a result. Perhaps this is a step too far for you at this point, but it demonstrates one of the many additional amazing capabilities that Excel has to offer in its current state.

Count the number of times a certain character appears using LEN and SUBSTITUTE Function

LEN and Substitute

For clarification, the SUBSTITUTE function substitutes the character a (second parameter) with the string “”. (third argument). LEN(SUBSTITUTE(A1,”i”,””) = 8 in this case (the length of the string without the character i). Taking this number and subtracting it from 9 (the total number of characters in cell A1), we obtain the number of instances of the character I that appear in cell A1.

Also Read: How To Use Sum And Count Function In Excel


Abhijit currently working with India's No. 1 Ecommerce Company Flipkart. He having 6 years experience in Excel and he is also expertise in various Advance Excel formulas. He awarded by his organization for his outstanding contribution in Analytical Project.

Leave a Reply