Do you use Excel regularly but are overlooking one of its most powerful features? The LEN function is a great tool for users seeking to calculate the length of text and numbers stored in their spreadsheet. By leveraging the LEN function, you can quickly learn how many characters have been inputted into a cell without manually counting them! In this blog post, we will explore what LEN does, how it works, as well as detailed examples that show you how to use this helpful Excel feature. Let’s dive right in and get started! You can find LEN under the TEXT group of functions in Excel.
LEN Function Syntax.
First let’s take a look at the function LEN. It is straightforward to implement.
=LEN(text)
Text is the text whose length you want to find. Remember, spaces count as characters!. The text argument can be provided from three sources.
- Directly.
- A string from another formula.
- As a reference to a cell containing a string.
Basic Example Of The LEN Funtion.
Let’s dive right in and walk through a simple example. In its most basic use LEN can be used to find out the length of a text string in an Excel cell. In the screen grab below the formula LEN in Excel is returning the length of the text strings from column A.
When I said the LEN function is used to return the length of the text string, EVERY character is included, so that means spaces, punctuation, numbers as well as letters.
So, if you are counting along the number of characters, you will see that Hello World is actually listed as having 11 characters in C3, but 13 characters in C4, that is because the characters in C4 string are actually “Hello World ” (with two spaces after World). This type of detail is important within the data world working with Excel formulas and functions. It can be time consuming and tedious to have to count characters. As can be seen, you can get Excel to do all of the hard work by using the LEN formula.
Other Case Uses Of LEN Function.
Using this function can help streamline workflows, as it allows users to easily extract the length of data quickly and accurately. Additionally, it can be used to compare different pieces of information or find out if there is any missing or unnecessary characters in strings.
T
This is another one of the core Text functions you should know in Excel. This function is used the return the length of the text string in question. You might wonder when you would use this, but as soon as you start to manipulate text in Excel, it is one of the the key functions that will be really useful in your Excel box of tricks.
Alexis says
What if you are trying to add the characters in 2 different cells (ie: A1 + B1)??
Barbara says
Alexis
Try the following formula
=LEN(A1)+LEN(B1)
Regards
Barbara
steven says
I found a formula in a book using len and trim to find sentence lengths, but the formula has not worked. Maybe a misprint? But this is my task: fidning sentence lengths in text. How?
Barbara says
Steven
I have used the following formula to find the length of sentences, or how many words in a sentence.
=IF(LEN(TRIM(B1))=0,0,LEN(TRIM(B1))-LEN(SUBSTITUTE(B1,” “,””))+1)
This assumes your sentence is in cell B1. The sentence says Have a nice day
The result would be 4. That is 4x words separated by spaces.
Hope this helps
Regards
Barbara
steven says
Thank you, Barbara. I will try it out!
endang says
How to use SUMIF with LEN.
Example :
———————————————————-
Column A Column B
———————————————————–
013325434 10000
0 500
0 500
012312D32 20000
———————————————————–
If Len(text) in column A = 9 so sum is 10000 + 20000 = 30000
How the function ? SumIF (A1:A4, (Len (A1:A4)=9),B1:B4) it’s not worked 🙁
jay says
need help, if the time is 8:45:00 AM using this formula
=IF(LEN(AF6)=7,LEFT(AF6,5)&” “&RIGHT(AF6,2),LEFT(AF6,4)&” “&RIGHT(AF6,2))
the answer should be 8:45 AM only
Joycelyn says
Do que mundo boas maneiras, intermédio do que moderação em prol de emagrecer,
a senhora irá preferir prevenir esses provisão, no entanto mesma coisa tubérculos todavia leguminosas, optando por vegetação, ovos e também proteínas no máximo a estabilização luto desventura.
Dennis Earl says
Having a problem with Len…
B16 = 5 The number is Category “General
I want to change the “5” to “05”
Cell F16 Formula: =IF(LEN(B16)=1,CONCATENATE(0,B16),B16)
When I type the formula in cell f16, nothing happens. Any idea as to why the formula is not working?
Barbara says
Dennis
You can try this version of the formula
=IF(LEN(B6=1),CONCATENATE(“0”,B16),B16)
Regards
Barbara
Thomas says
Great post! Have nice day ! 🙂 63v7l
speakerdeck.com says
Thanks for the marvelous posting! I truly enjoyed
readng it, you could be a great author.I will be sure to bookmark your blog and
will come back down the road. I want to encourrage yourself to continue your great posts, have a
nice evening!