EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
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

Updated May 30, 2023

LEN in Excel

LEN in Excel (Table of Contents)

Start Your Free Excel Course

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

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

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
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

Download LEN function Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download LEN function Excel Template

EDUCBA

डाउनलोड LEN function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more