How to Count Characters in Excel

Excel was designed to work with numbers. Therefore you can always choose one of three ways to perform any counting operation with digits.

Excel also has some text functions that can help when you are working with the text data. In some cases, you may need to calculate the total number of characters in a cell or the number of times a specific character occurs in a cell.

If you want to know how to count characters in Excel, you need to use the LEN function by using formula =LEN(cell) for counting total characters in a cell. And the combination of SUMPRODUCT function and LEN function for calculating total characters in a range with formula =SUMPRODUCT(LEN(range)).

Also, counting of a specific character in a cell and specific combination of characters in a cell are explained as follows:

1. Count Total Characters in a Cell
2. Count Total Characters in a Range
3. Count Specific Character in a Cell
4. Count Specific Character in a Range
5. Count Specific Combination of Characters in a Cell or Range

Count Total Characters in a Cell

If you want to get a total count of all the characters in a cell, you can use the LEN function.

The LEN function takes one argument, which could be the text in double-quotes or the cell reference to a cell with the text.

This function counts total characters in a cell, including spaces, punctuation marks, and symbols, despite how many times they occur in a string.

For example, suppose you have the dataset as shown below, and you want to count the total number of characters in each cell:

LEN function will count all the characters in a cell, such as a special character, numbers, punctuation marks, and space characters. That’s why sometimes you may get the wrong result if you have extra spaces in the cell.

For example, the LEN function returns 9 for the text in cell A3, while it should have been 7. But since it’s counting extra space characters as well, you get the wrong result.

If we want to count total characters in a cell, excluding spaces, the combination of formulas LEN and SUBSTITUTE will be required. The following formula excludes spaces from the text.

After that, it is easy to count total characters with the LEN function, with the final result of 7 characters without any space in the text.

Count Total Characters in a Range

Use the combination of SUMPRODUCT and LEN functions to count the total character number in a defined range.

The SUMPRODUCT function usage is an elegant solution whenever we are dealing with multiple cells or arrays.

For example, suppose you have the same dataset, and this time, instead of getting the number of characters in each cell, you want to know how many characters are there in the entire range.

The LEN part of the function takes an entire range of cells and counts each cell’s characters. And when you use the SUMPRODUCT function with it, it would add all these numbers.

Count Specific Character in a Cell

Besides the total character number, there is also the option in Excel for counting the number of occurrences of specific characters.

To count how many times a specific character appears in a cell, you can use a formula based on the combination of SUBSTITUTE and LEN functions. The cell contains the following formula:

For example, you have the following dataset, and you want to count the number of a specific character (i.e., “b”) in a defined cell A3.

In the above example, A3 represents the cell address, and “b” represents the character you want to count in the text.

• The LEN function is used to count the total number of characters in the A3 cell.
• SUBSTITUTE function is used to remove all occurrences of the character “b” in the A3 cell by replacing it with an empty string.
• The text’s length without the “b” character in the text is subtracted from the original text’s length.
• The result of the LEN(SUBSTITUTE(A3, “b”, ” “)) is then subtracted from LEN(A3). This gives the number of occurrences of the character “b” in cell A3.

NOTE: Excel SUBSTITUTE function is a case-sensitive function.

For example, cell A3 contains 5 occurrences of “b”, two are in the uppercase, and three are in the lowercase. If you supplied “B” to the SUBSTITUTE function in the same above formula, it gives a different count.

In the final result, the function is not counting lowercase characters.

Lower and Upper Case

The solution for counting characters without case-sensitive criteria is using the UPPER/LOWER function, where all characters will be translated to uppercase or lowercase, and the function will become case-insensitive.

In the below example, function LOWER is nested into SUBSTITUTE function, changing all string in cell A3 into lowercase, since criteria are defined as lowercase, “b”:

Another solution for making non-case-sensitive functions is using the double nested SUBSTITUTE function combined with the LEN function.

In the below example, counting characters will be in a specific cell because sometimes it is not practical to write each time to count a character in the formula, especially if you are dealing with complex ranges and formulas.

Formula with double nested SUBSTITUTE function:

The formula might be looking complex, but everything is explained below step by step.

• First, we want to eliminate the lowercase counting character from the text: =SUBSTITUTE(A3, LOWER(B3), “”)
• A LOWER function is put to translate counting character into lowercase, and then the +SUBSTITUTE function replaces the lowercase character into an empty string.
• To eliminate the uppercase counting character from the defined text. This is solved by nested SUBSTITUTE function: =SUBSTITUTE(SUBSTITUTE(A3, LOWER(B3), “”), UPPER(B3), “”).
• In the first SUBSTITUTE function, the lowercase character has been replaced with an empty string. In the second SUBSTITUTE function, the uppercase counting character is replaced with an empty string, resulting in the text without counting characters “b” and “B”.
• Now LEN function counts the number of characters from a modified text.
• In the final step, the evaluated formula result, text without “b” and “B”, is subtracted by the total number of characters in a defined cell.

Count Specific Character in a Range

Whenever you are dealing with cell ranges and arrays, the SUMPRODUCT function must combine other functions. For counting the specific character in a range, for case-sensitive counting, the combination of three functions is used: SUMPRODUCT, LEN, and SUBSTITUTE. And for case-insensitive counting, additional formulas should be added: UPPER/LOWER.

For example, consider the below dataset, and you want to count the number of occurrences of character “b” in a defined range (case-sensitive version).

The formula is almost similar to the formula explaining counting characters. The difference is coming from the LEN function nested in SUMPRODUCT function and counting area, instead of the cell is defined range of cells:

For case-insensitive character counting in a defined range, we can use the following formula:

NOTE: Whenever you are dealing with cell ranges, you will have to use the SUMPRODUCT function. For case-sensitive character counting in range combination of functions is needed: SUMPRODUCT, LEN, and SUBSTITUTE, and for case-insensitive counting: SUMPRODUCT, LEN, SUBSTITUTE, and UPPER/LOWER functions.

Count Specific Combination of Characters in a Cell or Range

There is also the possibility to count specific character combinations in a defined cell or range. We covered single character counting in a cell or range with case-sensitive and case insensitive alternatives in the above examples.

In a Cell

The formula for counting the combination of characters is the same. Only we have to divide it by the number of characters in the character combination.

For case insensitive version, the formula is:

The above formula is the same as counting a single character. Only we had to divide the regular formula with the number of specific characters that we are counting. As the final result, 2, which means “Te” has two times appears in the text.

And for the case sensitive version formula will look as follow:

There are two “bo” character combinations in the text, one in uppercase and the other in lowercase. But this formula is case sensitive. That’s why the final result gives 1.

In a Range

Counting combination of characters in a range has the same logic as counting a single character, and for the case sensitive version formula will look as follow:

In the A3:A6 range of cell two combinations of the “Ba” character is found.

In the case-insensitive version, the formula will look as follow:

In the above example, only one combination of uppercase “Ba” characters appears in the A3:A6 range.

---