Count Characters in Excel

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.

Count Characters in Excel example 1

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

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

Count Characters in Excel formula 2

Count Characters in Excel example 2

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

Count Characters in Excel example 3

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.

Excel formula 4

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

example 4-1

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 the 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.”

Recommended Articles

This has been a guide to Count Characters in Excel Cell. Here we discuss how to count total characters in an excel cell using LEN, SUMPRODUCT, and SUBSTITUTE Functions along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *