LEN Function In Excel

Excel Expert Course

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.

Excel Dashboard Reports

1. Using the LEFT function in Excel

2. Using the RIGHT function in Excel

3. Concatenation – understanding it’s power

Comments

  1. 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

  2. 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 :(

Leave a Reply

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