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
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
|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
|=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)|