LEN Formula: Return the Number of Characters in Excel

len, formula, length, excel

There are many scenarios in which you need to know how many characters does a cell contain. Excel provides an easy formula for counting the number of characters in a cell: LEN. Let’s take a look at how it works.

How to use the LEN formula

len, formula, excel
Basic use of the LEN formula

LEN returns the number of characters in a cell. It is very simple to use as the formula only has one part:
=LEN(B2)  returns the length of the text in cell B2. Instead of a cell reference you can of course use a text directly enclosed with “-signs.

The result of the LEN formula in the image above is 23 because the text in cell B2 “This cell contains text” has 23 letters.

Please note the following comments:

  • Space characters also count as characters.
  • Also, don’t mix it up with LENB, which returns the size of bytes and not characters.
  • You can also use the LEN formula with number cells. Say, cell B2 has the number 123.45 then =LEN(B2) returns 6 because the number 123.45 has 6 character, including “.”.

Use the LEN formula on multiple cells

array, formula, length, multiple, cells
Get the length of multiple cells

You can also use the LEN formula on several cells at the same time. Unfortunately, you have to apply it as an array formula. To achieve this, after typing the formula don’t just press Enter on the keyboard, but instead press Ctrl + Shift + Enter.

Example: Let’s say you have text in the cell range B2 to C4. You want to know how many letters are used in total and show it in cell E2.

  1. Type the formula =SUM(LEN(B2:C4)). Don’t type the curly brackets. Those will be added afterwards.
  2. Now press Ctrl + Shift + Enter on the keyboard. Excel automatically inserts the curly brackets.

How to remove the last letter of a cell if it is a space with formulas

blank, space, formula, remove, character, letter, cell, text
Remove the last character if it is blank with a formula in Excel

LEN is often used in combination with LEFT and RIGHT. For example, you have a column of names, but some of them got a space character at the end which you want to remove. We use a IF function to determine if the last character is a space. If yes, we remove the blank space. If no, we take the text unchanged.

For more information of how to remove the last letter if it is a space please refer to this detailed article.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

1 comment

  1. I have a sort problem. When I use LEN() to see how many characters are in a cell, sometimes it gives me a correct result, sometimes the length of the formula. Very crazy. My result is percentage to 2 decimals, so LEN() is 4. Sometimes. Sometimes it’s 17, which is the formula length. Both cells ate formatted as percent. Here’s the formula:
    =BJ20/(BJ20+BK20)

Leave a comment

Your email address will not be published. Required fields are marked *