If you have been searching for a guide on how to count number of characters in Excel then you are at the right place. The following tutorial demonstrates how to count characters in Excel. Furthermore, you will be enlightened about the easy formulas to count specific characters and the total number of characters in a cell or in a range with both the cases; sensitive and insensitive.
How to Calculate All the Characters in a Range?
To count number of characters in Excel (in a range), the most simple solution is to is to count the character for every single cell. Thereafter, you can add up the numbers shown below.
=LEN(A2)+LEN(A3)+LEN(A4)
Or
=SUM(LEN(A2),LEN(A3),LEN(A4))
This particular formula can work well for a range that is small. To count the total number of characters in a large range, you can use the SUMPRODUCT function. It multiplies the arrays and then reflects the sum of the products.
Below is the Excel formula to count characters in a large range.
=SUMPRODUCT (LEN (range) )
The real-life method should look like shown below.
=SUMPRODUCT (LEN (A1: A7))
By default, the SUM function is not capable to calculate arrays so you might need to use Ctrl + Shift + Enter keys in order to convert it into an array method.
How Does This Formula Work?
This is the most simple formula that you can use to count characters in Excel. The formula works in a way that the LEN function easily calculates the string length for every cell in the specific range and delivers them as an array of digits/numbers. Thereafter, SUM or SUMPRODUCT add up these numbers and delivers the total character count.
Note: The Excel LEN function calculates all the characters in each cell which includes punctuation marks, numbers, letters, special symbols, and all spaces (spaces, trailing, and leading, between the words).
How to Count Specific Characters in a Cell?
In Excel, you might need to count only the special number, specific letter, or special symbol. If you want to count specific characters in Excel in a cell then you can use the LEN function together with SUBSTITUTE for Excel count characters in cell as shown below.
=LEN(cell)-LEN(SUBSTITUTE(cell, character,””))
For instance, you manage the database of a few delivered items and each item has its unique identifier. And each of the cells carries numerous items that are separated by space, comma, or other delimiters. The duty is to count how often a given identifier displays in each cell.
If the list of delivered items is in column B (beginning in B2) and you are counting the number of A occurrences then the formula should be like this mentioned below.
=LEN(B2) -LEN(SUBSTITUTE(B2,”A”,””))
How Does This Formula Work?
- First of all, you can count the total string length in B2: LEN (B2).
- Thereafter, you can use the SUBSTITUTE function in order to remove all the occurrences of letter A in B2 by replacing it with the empty string (“”): SUBSTITUTE (B2,”A”,””).
- Once done, you can count the string length without the “A” character: LEN(SUBSTITUTE(B2,”A”,””)).
- In the end, subtract the length of the string without “A” from the total length string.
Consequently, you find the count of only removed characters that is equal to the total number of characters in a cell.
Case-insensitive Excel Formula to Count Characters in a Cell
If you ever require a case-insensitive character count then you can simply insert the UPPER function inside SUBSTITUTE so that it can convert the specified character to uppercase before running the substitution. However, you need to keep one thing into consideration that you use the uppercase character in the formula.
For example, if you count “A” and “a” items in cell B2 then use the formula mentioned below.
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),”A”,””))
Below is another way to use nested Substitute functions:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,”A”,””),”a”,””)
In a few cases, you will need to count numerous diverse characters in a table, but you will not need to alter the formula every time. In such case, nest one Substitute function inside another, type the character you need to count in a few cells and convert that cell’s value to uppercase and lowercase by utilizing the UPPER and LOWER functions:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), “”), LOWER($D$1),””))
As an alternate option, you can convert both the source cell and the cell containing the character either to uppercase or lowercase. For example:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),””))
The advantage of this approach is that in any case of whether the uppercase or lowercase character is input within the referenced cell, your case-insensitive character count formula will return the correct count.
Count Occurrences of Certain Substring or Text in a Cell
To count how many times a particular combination of characters (certain content, or substring) shows up in a given cell, e.g. “A2” or “SS”, at that point partition the number of characters returned by the over equations by the length of the substring.
Case-sensitive formula
=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,””)))/LEN($C$1)
Case-insensitive formula
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),””)))/LEN($C$1)
In the above formulas, B2 is the cell that carries the whole text string whereas C1 is the text (substring) you want to count.
How to Count Specific Characters in a Range?
If you want to know how to count specific characters in Excel then you can use the easy Excel LEN formula in order to count a specific char in a cell, put it within the SUMPRODUCT function that can easily handle arrays:
SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, character,””)))
In the example mentioned below, the formula takes the following shape:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, “A”,””)))
This is another formula to count characters in Excel in range.
=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, “A”,””)))
Compared to the primary formula, the foremost obvious distinction is utilizing SUM rather than SUMPRODUCT. Another contrast is that it requires pressing Ctrl + Move + Enter since unlike SUMPRODUCT, which is outlined to prepare clusters, Whole can handle clusters as it were when used in an array formula.
If you do not need to hardcode the character within the formula, you’ll be able to type it in a few cells, say D1, and reference that cell in your character count formula:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,””)))
Note: In case you count the occurrences of a specific substring in a range, for example, orders beginning with “KK” or “AA” then you need to distribute the character count by the substring length, otherwise, each character in the substring will be counted separately. For example:
=SUM((LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1, “”))) / LEN(D1))
How Does This Character Counting Formula Work?
For information, the SUBSTITUTE function is used to replace all occurrences of the specified character with an empty text string (“”). Later, we supply the text string returned by SUBSTITUTE to the Excel LEN function so that it can calculate the string length with no A’s. Thereafter, you subtract the character count from the total length of the text string.
The result of the calculation is an array of character counts along with one character count per cell. Lastly, SUMPRODUCT sums the numbers in the array and returns the total count of the specified character in the range.
A Case-insensitive Formula to Count Characters in a Range
As mentioned earlier, SUBSTITUTE is a case-sensitive function which makes the Excel formula for character count case-sensitive as well.
To count specific characters in a range ignoring case, you can also use any of the following formulas.
- First of all, you can use the UPPER function and then enter a character in uppercase form as shown below.
=SUMPRODUCT(LEN(B2:B8) – LEN(SUBSTITUTE(UPPER(B2:B8),”A”,””)))
- Apply nested SUBSTITUTE functions
=SUMPRODUCT(LEN(B2:B8) – LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),”A”,””),”a”,””)))
- Use UPPER and LOWER functions and type either an uppercase or lowercase char in any cell, and reference that cell in the formula as shown below.
=SUMPRODUCT(LEN(B2:B8) – LEN(SUBSTITUTE(SUBSTITUTE((B2:B8), UPPER($E$1), “”), LOWER($E$1),””)))
Conclusion
So, with the help of the aforementioned formulas, you will get past the very common ‘how to count the number of characters in an Excel cell’ question in a jiffy. To know about subtraction formula in Excel and how to group rows in excel with expand collapse, check out our other guides on this page.