Excel Character Count (Table of Contents)
Count Characters in Excel Cell
Many times we need to count the characters in the cells, this might be because we have some limits in excel or maybe this has been restricted by the user that certain characters should be entered only in the cells. Hence this becomes important that we know how we can count a number of characters in a cell in excel.
Counting the number of character in excel is different from count the filled cells in excel. Counting the number of characters in excel means that we need to know how many alphabets are entered into the cells and on the other side counting the filled cells would mean that counting those cells only that have text or some value in them. So this is important that we know the difference between counting cells and counting the number of characters of excel cells as both of these topics are different.
When we count a number of characters in a cell this means that we are counting the alphabets that are in that cell. This becomes sometimes important to know because excel has its own limits.
In excel a numeric value can be inserted or shown up to some limits and same is the case with the formulas, we can only execute that formula that has the character limit of a maximum of 8192 charters. So if in any case if we see that the formula is giving error we should count the character first.
In excel counting the characters are 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.
Explanation of Count Characters in Excel Cell
Using the LEN function is the easiest way to calculate the characters that are present in the cell as this function does not need only one argument that is we just need to specify the cell for which we need to calculate the characters.
While using the function of LEN we have various options about how we want to calculate the characters, we can choose to count all the characters or choose to exclude some of the characters.
Not only we can count characters from a cell, but we can also count the characters from the full range and this adds more usability to this function of LEN. While calculating the characters from the cell this should be noted that every character that is in the cell will be calculated and this means that even space inserted in the cell would be counted. Even if the space is not visible to us but this is still be counted as a character by the function of LEN.
The generic function that is designed to calculate the characters from the cells is the LEN, however, we can make some modifications to these functions to get the result as per the situation. If we have data where we have the text that includes some special characters and we want the special characters should not be counted then we can still make this happens. This just needs using the substitute function with the LEN function.
How to Count Characters in Excel Cell?
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 Instead from a Single Cell.
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”.
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 –
- Excel Insert Image
- Excel Formula for Grade
- SUMPRODUCT and Multiple Criteria
- Char In Excel
- REPLACE Function in Excel
- TEXT Formula in Excel
- How to Split Cells in Excel?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
