The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
LEN Formula: Get the Number of Characters in 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.
Contents
How to use the LEN formula
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 “.”.
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Or connect with me on LinkedIn!
Get Excel tips, tutorials and news!
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
Use the LEN formula on 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.
- 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.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
- Learn how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
How to remove the last letter of a cell if it is a space with formulas
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.
