EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources TEXT and String Functions in Excel LEN Function in Excel
 

LEN Function in Excel

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

LEN in Excel

LEN in Excel (Table of Contents)

 

 

  • Introduction to LEN in Excel
  • How to Use LEN Function in Excel?

Introduction to LEN in Excel

LEN function in Excel, where Len is taken from a length, is used to count the number of characters in a cell. LEN function considers special characters, spaces, and alphabets and counts all these values if a selected cell exists. The most important thing to know about the LEN function is it only counts the character length for only one cell. If we select any range of cells, we will get the #Value error.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

LEN Formula in Excel:

Below is the LEN Formula.

LEN Formula in Excel

LEN Function consists of only one parameter, i.e., TEXT

TEXT: The text you want to find the length of the characters.

Note: Space is also considered as one character.

How to Use the LEN Function in Excel?

This LEN Function is very simple and easy to use. Let us now see how to use the LEN function in Excel with the help of some examples.

You can download this LEN function Excel Template here – LEN function Excel Template

Example #1

In the below example, I have used the LEN function to count letters in a cell. “Good Morning!” has 12 letters with a space between them.

LEN Example 1-1

I got 13 on the result.

LEN Example 1-2

Example #2

In the below example, I have used the LEN function to count letters in a cell. Here I have taken a date as an example.

Example 2-1

Even though the above date has 10 characters, I got only 5. The reason behind it is that LEN Function counts the characters in the value of a cell and is not concerned with formatting.

LEN Example 2-2

LEN Example 2-3

LEN Example 2-4

Example #3

Now I will explain the huge benefit of the LEN function when you want to compare two similar sets of data sets.

LEN Function with TRIM is a beautiful combination in Excel. We have two similar values in cells A1 and B1, and we need to compare whether both are the same.

Example 3-1

The values in Cell A1 & B1 look similar, but comparing them in Cell C1 shows me FALSE. Looking at the outset looks similar, but not able to recognize the difference here.

LEN Example 3-2

The LEN function can save me here. Apply LEN to A1 and determine how many characters are in the A1 cell.

LEN Example 3-3

So in cell A1, 14 characters are there.

LEN Example 3-4

Apply LEN to B1 to find out how many characters are there in the B1 cell, and in cell B1, 13 characters are there

LEN Example 3-6

, which is why the formula shows it as two different values.

Because of one extra trailing space after the word Excel Formula, it is showing as 14 characters. We can remove unwanted spaces by using the TRIM function.

Trim Formula

Therefore, TRIM has removed the unwanted space and returned only the text value; now, our formula in cell C1 shows both values as TRUE.

LEN Example 3-8

Example #4

Below are the names of the employees, and you needed to extract the Last Name separately.

Example 4-1

In the above example, we cannot just apply RIGHT with specified numbers because the last name of every employee is different.

For the first employee, the last 4 characters are 5, but for the second one,e it is 11 (including space, i.e., Robert Adam), and for the third one, it is 12 (including space, i.e., J. Johnston)

Here we need to use FIND & LEN function to determine the number of characters from the given text.

LEN Example 4-2

and the answer will be:

LEN Example 4-3 

Similarly, We Find the other two

LEN Example 4-4

Right Formula

Part 1: This part determines the desired text that you want to extract from the characters.

Part 2: LEN function will give you the total number of characters in the list. We will see the detailed article on LEN in the upcoming articles.

LEN Example 4-6

The answer is:

LEN Example 4-7

Part 3: The FIND function determines from which number space begins, i.e., The End of the first name. We will see the detailed article on FIND in the upcoming articles.

LEN Example 4-8

The answer is:

LEN Example 4-9

Len gives 10 characters and Finds 5 for the first employee. That means LEN(E2) – FIND(F2) (10 – 5) = 5 characters from the right side; the result will be Smith

LEN Example 4-11

Len gives 16 characters and Finds 5 for the first employee. That means LEN(E3) – FIND(F3) (16 – 5) = 11 characters from the right side

LEN Example 4-12

the result will be Robert Adam

LEN Example 4-13

Len gives 17 characters and Find 5 for the first employee. That means LEN(E4) – FIND(F4) (17 – 5) = 12 character from the right side

LEN Example 4-14

the result will be J. Johnston

LEN Example 4-15

VBA Code to Use LEN Function

We can also use the MID function in VBA code, like Excel. The below code illustrates the usage of the LEN function in VBA macros.

Sub LEN_Function_Example
Dim Length_String as string
Len_String = LEN (“Data Analysis & Reporting”)
Msgbox Len_String
End Sub

If you run the above code message box will display 25 as your result.

Things to Remember

  • LEN Function is useful when you count the number of characters in some text.
  • Formatting of numbers is not a part of the LEN function, i.e., if your value shows $1500.00, then it will show as 4 only instead of 8.
  • Numbers and dates will also return a length.
  • If a cell is blank,

Example 5-1

it will return 0 in the result.

Example 5-2

  • A cell with an error value will return that error in the result.

Example 7-2

  • You can refer to only one cell at a time.

Example 6-1

Otherwise, it will show an error as #Value.

Example 6-2

Recommended Articles

This has been a guide to LEN Function. Here we discuss the LEN Formula and how to use the LEN function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

  1. AND Function in Excel
  2. LOOKUP in Excel
  3. VBA Length of String
  4. LEN Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download LEN function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download LEN function Excel Template

EDUCBA

डाउनलोड LEN function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW