Excel LEN Function to measure length of text

What does Excel LEN Function do?

Excel LEN Function is to measure length of a text.

Why do you need Excel LEN Function?

Excel LEN Function is not quite meaningful to use alone, it is more of a stepping stone to achieve another purpose. LEN Function is most commonly used with the following functions in order to extract a substring from a string.

LEFT Function – extract a substring on the left

RIGHT Function – extract a substring on the right

MID Function – extract a substring in the middle of the string

FIND Function or Search Function – return starting position of a substring (first occurance) in a string

Here is a common use of LEN Function: you have an address which has a common at the end, for example,

Flat A, ABC Street,

Now your goal is to remove the comma at the end of the address. You know that you should use LEFT Function to extract the substring, but how many text should you extract on the left? You need LEN Function to count the total length of the whole text (19 characters), and then minus by 1 (18 characters).

MID Function is even more important in VBA in looping through each character within the string for manipulation. In a For…Loop statement, you have to specify the number of loops, and LEN Function is used in that way.

Syntax of Excel LEN Function

LEN(text)
text The text string from which you want to return length

Example of Excel LEN Function

To simply the formula, assume Cell A1 contains address Flat A, ABC Street,

Cell B1 contains a full name Peter, Gilbert

Formula Result Explanation
=LEN(A1) 19 Total 19 characters in the string (including space)
=LEFT(A1,LEN(A1)-1) Flat A, ABC Street Extract substring from left excluding the ending comma
=MID(“Peter,Gilbert”,FIND(“,”,”Peter,Gilbert”,1)+1,LEN(“Peter,Gilbert”)) Gilbert Extract substring from (comma position+1) to (length of whole string)

Outbound References

http://www.techonthenet.com/excel/formulas/len.php

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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