How To Count Characters In Excel

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.

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

SUM and LEN in Excel

Using ARRAY

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

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

Count Specific Character 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.

Count Specific Character 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).

Case Insensitive Character Count

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

Lio

Maximize Your Online Business Potential for just ₹79/month on Lio. Annual plans start at just ₹799.

Leave a Reply

Your email address will not be published. Required fields are marked *