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:


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.


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.

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: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks