Counting Characters in Excel by LEN()

First example: Counting single cell characters

Let me start with a simple example of counting characters in Excel. For that, a text string is entered in the A2 cell that contains alphabets, numbers, and spaces. See how LEN function returns character count:

The LEN formula:

You can see, that LEN returned the characters count of “Hello U123, how is it going?” string as 28.

Get the count of multiple cells independently

If you require the character count of multiple cells, then simply copy and paste the above LEN formula into the cells where you want to display the count and replace the A2 with the corresponding cell (which character count is required).

For example, I used the =LEN(A2) formula in the B2 cell in the above example.

Just copy the B2 cell; after selecting B2 cell, press ctrl+C. Now paste the formula in B3, B4, and so on.

In the formula, the A2 should be replaced by A3 for the B3 formula, and so on automatically. See the example below:

Alternatively, after selecting the B2 cell, bring the mouse to the right bottom of B2 till a thin solid line + sign appears.

Now, drag the mouse down to the desired cell. The formula should be copied and A(cells) should update automatically for the respective B cells as shown below:

The example of getting the SUM of multiple cells' character count

If you require the sum of all cell’s character count, use the LEN function with the SUM function. To demonstrate that, let me extend the above example and display the sum of the character count of each cell in B6 cell.

The formula used in B6 cell:

The sum of A2, A3, and A4 cells is 49 characters.

Another way of getting the SUM by range in LEN

Rather than entering the LEN formula for each cell separately, you may specify a range in the LEN function. For example:

To get SUM this way, you need to follow these steps:

Step 1:

Select the cell where you want to display the character count. For the example, I selected B6.

Step 2:

Enter the above formula in the formula bar and press Ctrl + Shift + Enter key. This should display the sum of the letters count. If you enter the formula manually, it will not work.

A formula to count words in Excel

Well, you may refine the following formula further as per the complexity of the cell’s text. However, for the general text, you may use this for counting the number of words in Excel.

The formula for counting words in A5 cell:

The formula is the combination of LEN, TRIM, SUBSTITUTE, and IF functions that work as follows.

The result as different texts are entered in A column cells:

You can see, it counted & =  signs as words (used in A5). The number in the A3 cell is taken as one word. The third column in the sheet displayed the number of characters.

How to get the count of a specific character?

In order to get the count of the specific character, you may use a combination of formulas. One of the ways is using the LEN with SUBSTITUTE function as follows.

In the example, the text where we want to count a specific character(s) is given in the A2 cell. The character(s) that we want to get the count of is entered in the B2 cell.

The following formula was applied in the C3 cell that displayed the occurrence of the specific character.

The resultant Excel sheet:

The ‘C’ occurred twice in the text string. This is how it worked: