Monday, December 21, 2020

Count characters in cells

When you need to count the characters in cells, use the LEN function—which 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, then press Enter on your keyboard.

Multiple cells: To apply the same formula to multiple cells, enter the formula in the first cell and then drag the fill handle down (or across) the range of cells.

Entering mulitple LEN functions in a worksheet

To get the a total count of all the characters in several cells is to use the SUM functions along with LEN. In this example, the LEN function counts the characters in each cell and the SUM function adds the counts:

=SUM((LEN( cell1 ),LEN( cell2 ),(LEN( cell3 )) )).

Give it a try

Here are some examples that demonstrate how to use the LEN function.

Copy the table below and paste it into cell A1 in an Excel worksheet. Drag the formula from B2 to B4 to see the length of the text in all the cells in column A.

Text Strings

Formulas

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

  1. Click cell B2.

  2. Enter =LEN(A2).

The formula counts the characters in cell A2, which totals to 27—which includes all spaces and the period at the end of the sentence.

NOTE: LEN counts any spaces after the last character.
 

Count characters in multiple cells

  1. Click cell B2.

  2. Press Ctrl+C to copy cell B2, then select cells B3 and B4, and then press Ctrl+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 (20, 27, and 45).

Count a total number of characters

  1. In the sample workbook, click cell B6.

  2. 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 (92).

No comments:

Post a Comment