How to Count Characters in Excel Cell? (using LEN Excel Function)

How to Count Characters in Excel Cell? (with Examples)

In excel counting the characters is very easy, for this, we can use the internal formula of excel that is called “LEN”. This function will count the Letters, number, characters and all spaces that are present in the cell. Since this function counts everything that is in the cells, this becomes important that we should know how we can exclude some of the alphabets or value that are in the cells.

Using this LEN function we can easily get the count of characters that are in excel cell.

You can download this Count Characters Excel Template here – Count Characters Excel Template

Example #1 – Count Total Characters in a Cell

To count the number of characters in a cell we can simply use the function of LEN

=LEN(Cell)

Where “Cell” will mean the location of the cell for which we need to calculate the character.

Example #2 – Count all Characters Excluding a Specific Character.

For this, we need to use substitute excel function inside of LEN.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #3 – Counting Only a Specific Character

To exclude some the characters from the count we just need to subtract the count of whole characters from the count of characters excluding specific characters

The first LEN will give the complete count and the second part of the function will give us the count of cells excluding “@”.

At last, we will have the count of specific characters.

Example #4 – Counting all Characters of a Range

The Len function is not capable of handling the Arrays, hence we cannot use this function to calculate the count of the whole of the range.

So we need some function that is capable of handling the arrays, arrays mean a source of data. So we will use the function of SumProduct that is very much capable of handling Arrays.

Sumproduct will sum the count of all LEN functions and hence we will get the count of the complete range.

This function will execute by calculating the characters of all the cells that are in the range, once this is done and the function has calculated the characters for all of the cells it will move to the function of SUM and will sum up all the character count and hence that is why we will get the character count of complete range.

Things to Remember

“Space” in the cell is also counted as a character by the LEN function.
While using the substitute function this should be remembered that this function is case sensitive. This means that “A” is not treated or searched as “a”.