Jun 26 2007

Excel Length Formula

The Excel Length formula is a useful formula that can be used in many different circumstances. I’ll be showing a few of the useful examples of it below. But first let’s take a look at the formula itself:

LEN(text)

where text is any string you want to find the length of. This formula is really easy to use, and the hardest part may be remembering that to use this formula it is LEN(text) and not LENGTH(text), as you will receive an error if you try to use it this way.

EXAMPLES

Example 1: =LEN(“Bird”) will return 4, since there are 4 characters in the string “Bird”

Example 2: =LEN(123456) and =LEN(“123456″) will both return 6, as the formula sees both of these as strings and not the first one as a number.

Example 3: =LEN(“Excel Help”)will return 10, because in the string there are 9 letters and a space, which is included in the count for this formula.




[ad#in-post-ad]
Length Formula Examples

Example 4: =LEN(A2) will return 3

For the next example, let’s take a look at column B. The Product Name consists of a color and an object. Using the LEN formula we can break this product into to different sections one being Color and the other being Product Name. There are many ways to accomplish this, this is just one example.

To break the color off of the product name, you could do the following:



Example 5: =LEFT(B2, find(” “,B2)) will return “Yellow”. We did not have to use the LEN formula in this example but we will to break the object name off of the product name.

Example 6: =RIGHT(B2, LEN(B2)-Find(” “,B2)) will return “Ball”.

Here is more information on the Left Formula or Right Formula

We will soon be including a more in depth example of the Find Formula as well.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

14 Comments on this post

Trackbacks

  1. Track Compelling Content Titles with 12-Step Title Optimization wrote:

    [...] and title length were easy to separate automatically on Excel, by using the sort function and the length formula, respectively. However, title types like “uses a person or brand name” involved a human [...]

    January 17th, 2013 at 7:00 am
  2. Compelling Content Titles: 12 Steps to Creating a Title Report wrote:

    [...] and title length were easy to separate automatically on Excel, by using the sort function and the length formula, respectively. However, title types like “uses a person or brand name” involved a human [...]

    May 16th, 2013 at 1:38 pm
  1. ganesh dhyani said:

    mor help regading function

    February 13th, 2008 at 7:24 pm
  2. D Dixon said:

    I was going nuts with the RIGHT fcn because I dared to ask myself how I would find the last words. But I see from the formula, the text has to be just two words long with a space in between to work. Is there a way for this or any function to work on longer text.
    For example, we have the string “Let’s play a game”
    Is there a formula out there that would be able to tell me that the last word in that string is “game”?
    HOW! Please share because this question is driving me nuts!

    October 8th, 2009 at 3:31 pm
  3. john said:

    Hi D – try this formula:

    =IF(ISERROR(FIND(” “,A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))))))

    It will return the word if there is only 1 word, or the last word if there is a series of words. Hope that helps.

    October 8th, 2009 at 4:26 pm
  4. john said:

    P.S. You might have to replace the quotes with quotes in excel if the copy/paste doesn’t work correctly.

    John

    October 8th, 2009 at 4:26 pm
  5. revista literaria hotel said:

    Is there some equivalent in open officce?

    July 12th, 2011 at 5:10 pm
  6. Pranai said:

    How do i measure the length of 5-Aug-11 as when i use len(cell),it only gives me 5?please help

    October 16th, 2011 at 9:55 am
  7. Kyle said:

    Can I change the width of a cell mid collumn using a similar formula and leave thee cells above or below unchanged?

    thanks

    May 25th, 2012 at 10:22 am
  8. moses said:

    Hi Josh, I have a similar issue.

    I need to restrict the length of a cell to 80 characters. this could easily be done left(a1,80), but I want to make sure no words are cut off.

    So it needs to check if position 80 or 81 is a space, then it could do left(a1,80),

    else it should check for the next last space prior to 80.

    July 12th, 2012 at 2:07 pm
  9. moses said:

    Hi Josh,

    This is the formula that worked for me.

    please let me know if you have any better options.

    =IF(ISERROR(FIND(” “,A1)),A1,IF(MID(A1,81,1)=” “,LEFT(A1,80),LEFT(A1,FIND(“*”,SUBSTITUTE(LEFT(A1,80),” “,”*”,LEN(LEFT(A1,80))-LEN(SUBSTITUTE(LEFT(A1,80),” “,””)))))))

    July 12th, 2012 at 2:33 pm
  10. moses said:

    The quotes screwed up that last display.

    =IF(ISERROR(FIND(” “,A1)),A1,IF(MID(A1,81,1)=” “,LEFT(A1,80),LEFT(A1,FIND(”*“,SUBSTITUTE(LEFT(A1,80),” “,”*”,LEN(LEFT(A1,80))-LEN(SUBSTITUTE(LEFT(A1,80),” “,”“)))))))

    July 12th, 2012 at 2:35 pm
  11. craig said:

    Hi there, I have forgotten a useful formula that used LEN and RIGHT together can someone help please?

    I have a long data list, that ends with bracketed charachters “……. (ABCD)”, “…….. (ABCE)” and I want to extract just the 4characters inside the parentheses.

    I can do it in 2-steps, Right(cella1,5) the left(cell,4) but feel 1 step is cleaner.

    Any ideas? Like I said, I forgot and it bugs me!
    thanks, Craig

    October 11th, 2012 at 8:56 pm
  12. Achyutanand Khuntia said:

    to using left function pull the word

    October 27th, 2012 at 3:11 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives