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.
Contents
How to use 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
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.
- Type the formula =SUM(LEN(B2:C4)). Don’t type the curly brackets. Those will be added afterwards.
- 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
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.
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)