How To Count Characters In Excel

It is easy to calculate characters on Microsoft Word documents but when it comes to Excel, it can be a little tricky. But nothing is too difficult and can be done in many ways. 

Let us learn in the article how to count characters in Excel.

How To Count Characters In Excel

Use the LEN function to count characters in Excel. Use LEN and SUBSTITUTE to count specific characters in Excel.

Using LEN Function

The LEN function in Excel counts the number of characters in a cell.

It counts 2 numbers, 1 space and 6 letters.

Using SUM and LEN Function

Use SUM and LEN to count the number of characters in a range of cells.

Using ARRAY

The array formula below counts the number of characters in a range of cells.

Complete an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. 

Using LEN and Substitute Function (For Specific Character Count)

Use LEN and SUBSTITUTE to count how many times a specific character (in this example, character a) occurs in a cell.

The SUBSTITUTE function replaces the character a (second argument) with an empty string (third argument). LEN(SUBSTITUTE(A1,”a”,””)) equals 8 (the length of the string without the character a). If we subtract this number from 9 (total number of characters in cell A1), we get the number of occurrences of the character an in cell A1.

Using LEN and Substitute Function (For Specific Character Count in a Column, Lower Case)

The array formula below counts how many times a specific character (in this example, character a) occurs in a range of cells.

The array constant {1;1;2;1} is used as an argument for the SUM function, giving a result of 5. The SUBSTITUTE function is case-sensitive. The A in Alaska is not counted.

Using LEN and Substitute Function (For Specific Character Count in a Column, Lower & Upper Case)

The array formula below counts both lower and upper case occurrences of a specific character (in this example, character a).

Now you can easily count characters in Excel and make your work more efficient.