How to count the number of characters in a cell using the LEN function in Excel [Tip]

Excel doesn’t have a word count, but the LEN function is something that counts text characters in cells. That counts all the text in a selected cell and returns the value. In addition, you can also combine the LEN formula with the LEFT or RIGHT functions to remove text from cells.

Open an Excel spreadsheet, and enter blue pencil into a cell. Now click a cell to add the LEN function to, and press the Insert Function (fx) button. That will open the window in the shot below.

LEN

Now select LEN from that window and press OK. Then the window in the shot below will open. Press the button beside the Text box to select the blue pencil cell.

len2

Press the OK button to close the window. Now the LEN cell will include 11 in it as shown below. As such, there are 11 characters in the blue pencil cell including the space.

LEN3

You can also combine that formula with the RIGHT function. For example, select a cell and enter =RIGHT(E9, LEN(E9)-FIND(” “,E9)) in the function bar. If you did not enter blue pencil in cell reference E9, erase E9 from that formulae and add the cell reference with blue pencil in it. Then the selected cell will include pencil, but not blue, as below.

LEN4

When you edit text in Excel 2010/2013, the LEN function can be handy a addition to your spreadsheets. Anyways, you may also check out this YouTube page to watch a short video demonstration of the said function.

Related Posts