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.

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.

Let’s look at an example or two to prove how this works.

A range of text strings has been types in cells A2 to A5, these are under the column header Data Source. Column B holds to formula**=LEN(A2) **etc. The result of the formula for clarity is contained in Column C.

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 kind of detail is needed if you are dealing with formulas, and 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.

### More Excel Tips You Might Like.

1. Using the LEFT function in Excel

2. Using the RIGHT function in Excel

3. Concatenation – understanding it’s power

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