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