# Counting Characters in Excel by LEN()

What is LEN() function in Excel?

To count the number of characters in single or multiple cells, you may use the Excel LEN function.

In the LEN function, you may provide a text string, or cell or use LEN with SUM function for counting characters in multiple cells.

### First example: Counting single cell characters

Let me start with a simple example of counting characters in Excel.

A text string is entered in the A2 cell containing alphabets, numbers, and spaces.

See how the LEN function returns character count:

The LEN formula:

=LEN(A2)

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 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 the 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 the B6 cell.

The formula used in B6 cell:

=SUM(LEN(A2), LEN(A3),LEN(A4))

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:

=SUM(LEN(A1:A4))

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:

=IF(LEN(TRIM(A5))=0,0,LEN(TRIM(A5))-LEN(SUBSTITUTE(A5,” “,””))+1)

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

1. The SUBSTITUTE replaces all occurrences of spaces to “” i.e. returns text with removed spaces.
2. The TRIM function removes any unwanted spaces from the specified text/cell.
3. The LEN method is used to get the length with spaces and without spaces.
4. Whereas, the IF function checks the initial length of text in the cell. If it is empty, the result is displayed as 0.
5. If the result is non-zero then the length before and after the SUBSTITUTE function is subtracted and added by 1; that is the count of words.

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.

=LEN(A2)-LEN(SUBSTITUTE(A2,B2,””))

The resultant Excel sheet:

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

• The LEN function got the total length of the A2 cell.
• After that, the character that we want to count is replaced by “” by using the SUBSTITUTE function.
• So, it returned the length of A2 after the replacement of the given term.
• Finally, the length of A2 after and before replacement is calculated which is the count of the given character.