How to Count the Number of Characters in Microsoft Excel
Any time you select multiple cells in Excel 2010 or 2013, the status bar displays a few calculations, including a count of selected cells and the sum of all selected numbers. If you want to know the number of characters inside your cells, however, you need to use the LEN function. LEN only counts one cell at a time, so to add up multiple cells, either use an AutoSum or the SUMPRODUCT function.
Select a cell adjacent to the cell you want to count and type "=LEN(" without quotation marks. The LEN function counts the number of characters in a target cell.
Click the cell that needs counting to enter its location into the formula, or type the location by hand. Press "Enter" to finish the formula and count the cells.
Drag the fill handle on the cell containing LEN to fill in additional cells with the same formula, counting the characters in the corresponding adjacent cells.
Select all the cells containing LEN formulas, open the "Formulas" tab and click "AutoSum" to insert a new cell with the complete character count.
Enclose a LEN function targeting multiple cells within the SUMPRODUCT function to produce a complete character count without the need for multiple individual LEN functions. To create this formula, type "=SUMPRODUCT(LEN(" without quotes, click and drag to select the target cells, close both parentheses and then press "Enter."
Tips & Warnings
- LEN counts letters, numbers and punctuation, including spaces.
- Click a row or column header while creating the SUMPRODUCT and LEN formula to count the characters in an entire row or column.
- SUMPRODUCT and LEN can't count every cell on an entire worksheet. Even if you place the formula on a separate sheet to avoid a circular reference, Excel cannot process the function on an entire sheet simultaneously. Instead, stick to counting smaller selections of rows or columns.