- Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013.
In this post you will learn how to count characters in cells, using the LEN function and SUM function.
Counting characters using the LEN Function
For any time you have to count the characters in cells, the LEN function comes in handy. This function allows you to count letters, numbers, characters, and all spaces. For example, the length of “It’s 98 degrees today, so I’ll go swimming” (excluding the quotes) is 42 characters—31 letters, 2 numbers, 8 spaces, a comma, and 2 apostrophes.
To apply the function, type =LEN(cell) in the formula bar, then press Enter on your keyboard.
Counting characters using LEN in multiple cells
Multiple cells: To employ the same formula to several cells, input the formula in the first cell and then drag the fill handle down (or across) the entire cell range.
Using SUM functions and LEN functions in multiple cells to count characters
To obtain the overall count of each of the characters in multiple cells, you can use the SUM functions in conjunction with LEN. In this case, the LEN function counts the characters in every cell and the SUM function sums the counts:
=SUM((LEN( cell1 ),LEN( cell2 ),(LEN( cell3 )) )).
Give it a try
Listed below are some examples that indicate how to deploy the LEN function.
Copy this table underneath and paste it into cell A1 in a brand new Excel worksheet. Drag the formula from B2 to B4 to notice the length of the text in each of the cells in column A.
|The quick brown fox.||=LEN(A2)|
|The quick brown fox jumped.|
|The quick brown fox jumped over the lazy dog.|
Count characters in one cell
- Select cell B2.
- Type =LEN(A2).
The formula counts the characters in cell A2, which reaches a grand total of 27—which contains all spaces and the full stop at the end of the sentence.
NOTE: LEN counts any spaces after the final character.
Count characters in multiple cells
- Choose cell B2.
- Press Ctrl+C to copy cell B2, then pick cells B3 and B4, and then press Ctrl+V to paste its formula into cells B3:B4.
This transfers the formula to cells B3 and B4, and the function counts the characters in each cell (20, 27, and 45).
Count a total number of characters
- In the example workbook, select cell B6.
- In the cell, type =SUM(LEN(A2),LEN(A3),LEN(A4)) and press Enter.
This counts the characters in all the three cells and sums them (92).