When you need to count the characters in cells, use the LEN function. The function counts 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 use the function, enter =LEN(cell) in the formula bar and press Enter. In these examples, cell is the cell you want to count, such as B1.
-
To count the characters in more than one cell, enter the formula, and then copy and paste the formula to other cells.
-
To count a total number of characters in several cells, use the SUM functions along with LEN. For example, =SUM((LEN(cell1),LEN(cell2),(LEN(cell3)))). In this example, the LEN function counts the characters in each cell and the SUM function adds the counts.
-
To count certain, specific characters within cells, use the LEN function with the SUBSTITUTE function. For example, if you wanted to know how many lower case Zs were within a cell you'd use this formula: =LEN(cell1)-LEN(SUBSTITUTE(cell1,"z",""))
Give it a try
Type the following data into a new Excel worksheet. Start in cell A1.
|
Text Strings |
Formulas |
|
The quick brown fox |
|
|
The quick brown fox jumped |
|
|
The quick brown fox jumped over the lazy dog |
Count characters in one cell
-
Click cell B2.
-
In the cell, enter =LEN(A2) and press Enter.
The formula counts of the characters in cell A2, and that number is 19, including all spaces. If you have spaces after the final period, the formula also counts them.
Count characters in multiple cells
-
Press CONTROL + C to copy cell B2.
-
Press CONTROL + V to paste its formula into cells B3:B4
This copies the formula to cells B3 and B4, and the function counts the characters in each cell (26, and 44).
Count a total number of characters
-
Click cell B6.
-
In the cell, enter =SUM(LEN(A2),LEN(A3),LEN(A4)) and press Enter.
This counts the characters in each of the three cells and totals them (89).
To count certain, specific characters within cells
-
Click cell C4.
-
In the cell, enter =LEN(A4)-LEN(SUBSTITUTE(A4,"z","")) and press Enter.
In short, this counts all lower case Zs in cell A4, which is 1.
