LEN LEFT and RIGHT function in Excel with Basic Excel Examples

Hi Friends,

In this article I shall discuss about few small, easy but useful Excel functions. Proper combination of these function can solve a larger problem within seconds. Today I'll discuss about three functions LEN(), LEFT() and RIGHT() step by step by the help of  two basic Excel examples. And in my next article I'll show you how easily and quickly we can use these function to break a complicated data into smaller pieces, but before that lets' understand these functions first.

Description

The Microsoft Excel LEN function returns the length of a specified string, LEN meaning LENGTH. The LEN function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

=LEN(text)

Very simple syntax !!! Isn't it ?

The working of this function is like you enter a text within this function and it will tell you the length of the text. 

But what if I enter a combination of numbers like 1132487 or number and text which we call alphanumeric like FED10093452d17HH.

Remember space is also treated as a character, therefore, while counting the length of a string by LEN function you have to count it.

This LEN function will count how many characters' are in the supplied string. Very simple to use, just type equal to sign (=) and type three characters LEN, put a tab, it will show a opening bracket. Now, select the cell for which you want to get the number count of characters or numbers or both or alternatively you can type the text within this function but don't forget to cover it by double quotes.

So, lets' take an example.  As you can see from the below images that I have four column headers Product Code 1, Product Code, LEN function Example 1 and LEN function example 2.

I need two data in last two columns the count of characters in column 'A' & column 'B' in column 'C' & 'D' respectively.

How to use LEN function in Excel

How to use LEN function in Excel to measure Length of  cell content

I hope you have understand this function very well. So lets move to the next two functions LEFT and RIGHT.

In this section I'll discuss about two functions together one after another and step by step as these functions are very similar in nature the difference is one and I'll show this difference in this article.

LEFT() and RIGHT() functions are also categorized as a String/Text Function.

Syntax

=LEFT(text, number of characters) and =RIGHT(text, number of characters)

I am discussing these function in one time because these functions are very similar as you can see from the syntax of these functions only difference is that they work oppositely in direction.

Parameter

text - input any text (you can enter number also it will work)

number of characters : this is the number that you want to get from the input text.

Let us have an example to make it more clearer.

=LEFT(text, number of characters) : As the name of this function indicates it is related to LEFT side of a text string and actually it gives you that number of characters from a string from left side which you instruct this function to show.  What it does is that if you enter a string as first parameter in LEFT function and number of characters you need to cut from left side of that text  input as second parameter then this function will give you out of that number of character as you mentioned in the number of characters parameter.

The below example will make it clear.

The working of the RIGHT() function is same, the only difference is that it will give you the output from the right side of the text.

One more thing, you can also enter the text input as

=LEFT("ADVANCED",3) or =RIGHT("ADVANCED",3)

The output of these formula would be 'ADV' for LEFT function and 'CED' for RIGHT function. Because the three character from lefet side of the text "ADVANCED" is 'ADV' and similarly from right side it becomes 'CED'.

So lets' see the below examples.

How to use LEFT Function in Excel

How to use RIGHT Function in Excel

Now what if I need to get the data from the middle of a string for example, if I have been given E1481210045089M52 as my input data and is asked to get the figures after 4 character upto 5 character (the red colored characters). Then what to do ?

Simple, I need to make a nested formula using LEFT and RIGHT function. But here is a trick, let us analyze the question first.

I need 5 characters from the 5th character of the given string this means that there are two steps, one is I need to get the 9 (nine) characters from the left side i.e., upto E14812100 and then I need to extract five characters from right side i.e., 12100 and my problem will be resolved.

If I write these steps in the form of formula the this will look like : =LEFT("E1481210045089M52",9) for step one and the output would be E14812100. And now if I cover it by RIGHT function then it will look like =RIGHT(LEFT("E1481210045089M52",9),5) and the result will be 12100. 

Please follow the image below, it will clear this thing easily and quickly. For ease I have shown three steps first is LEFT function, second is RIGHT function and the last one is nested LEFT and RIGHT function.

LEFT and RIGHT function combined

Hope you have enjoyed this article. In my next article I will show you few more examples on these three functions i.e., LEN, LEFT & RIGHT in more detail.

Thank you ... Stay Blessed....