Learn Use of Left(), Right() & Mid() Function in Excel

Have you ever felt the need to find few characters from a string either from the left side or from the right side or few characters from the mid of it? If the answer is Yes, then you should learn about the Left(), Right() and Mid() function.

Left() Function: Left function helps you find one or more characters from the start of a string.

The syntax of the Left function is as follows: '=Left(text,[num_chars])'

It has 2 arguments. The first one is "text". We pass here the string itself or the reference of the cell which holds the string. The second one is "[num_chars]". Here we pass digits only for the number of characters we want to find out from the start of the string. This is an optional argument. If we don't pass this argument, the left() function will return only 1 character from the start of the string.

Note: Arguments in square brackets '[ ]' are always optional for all functions. 

Ex-1

Let's write the below formula in cell A2 on sheet1 in Excel.

=left("Sujeet Jaiswal") 

Note: We always pass a string in double-quotes in the function or formula.

When we press enter on the keyboard.

We get a single character in the result from the start of the string i.e. S.

Here we have not passed the second argument. Therefore, the left() function returned only 1 character from the start of the string.

Ex-2

Let's write Sujeet Jaiswal in cell A1 and write the below formula in cell A3 on sheet1 in Excel.

=left(A1,5)

When we press enter on the keyboard.

We get 5 characters in the result from the start of the string i.e. Sujeet.

Right() Function: Right function helps you find one or more characters from the end of a string.

The syntax of the Right function is as follows: '=Right(text,[num_chars])'

It has 2 arguments. The first one is "text". We pass here the string itself or the reference of the cell which holds the string. The second one is "[num_chars]". Here we pass digits only for the number of characters we want to find out from the end of the string. This is an optional argument. If we don't pass this argument, the right() function will return only 1 character from the end of the string.

Ex-1

Let's write the below formula in cell B2 on sheet1 in Excel.

=right("Sujeet Jaiswal") 

When we press enter on the keyboard.

We get a single character in the result from the end of the string i.e. l.

Here we have not passed the second argument. Therefore, the right() function returned only 1 character from the end of the string.

Ex-2

As we have already written Sujeet Jaiswal in cell A1, let's write the below formula in cell B3 on sheet1 in Excel.

=right(A1,7)

When we press enter on the keyboard.

We get 7 characters in the result from the end of the string i.e. Jaiswal.

Mid() Function: Mid function helps you find one or more characters from the mid of a string and also from the start or end of the string.

The Mid function is more powerful than the Left and Right function. You may use it just like the Left and Right function.

How? We will see it later.

The syntax of the Mid function is as follows: '=MID(text,start_num,num_chars)'

It has 3 arguments. The first one is "text". We pass here the string itself or the reference of the cell which holds the string. The second one is "start_num". Here we pass the starting position (in digits) of the character or the string which we want to find from the string. The third one is "num_chars". Here we pass the number of characters we want to find (in digits) from the string.

Ex-1

Let's write Sujeet Kumar Jaiswal in cell C1 and write the below formula in cell C2 on sheet1 in Excel.

=mid("Sujeet Kumar Jaiswal",8,5) Or =mid(C1,8,5)

Here we want to find Kumar from mid of the string. When we press enter on the keyboard.

We get 5 characters in the result from the mid of the string i.e. Kumar.

8 is the starting position of the string and 5 is the number of characters we want to find from mid the string.

Ex-2

Use Mid Function As Left Function

Let's write the below formula in cell C3 on sheet1 in Excel.

=mid("Sujeet Kumar Jaiswal",1,6) or =mid(C1,1,6) 

Here we want to find Sujeet from the start of the string. When we press enter on the keyboard.

We get 6 characters in the result from the start of the string i.e. Sujeet.

1 is the starting position of the string and 6 is the number of characters we want to find from the start of the string.

Ex-3

Use Mid Function As Right Function

Let's write the below formula in cell C4 on sheet1 in Excel.

=mid("Sujeet Kumar Jaiswal",14,7) or =mid(C1,14,7) 

Here we want to find Jaiswal from end of the string. When we press enter on the keyboard.

We get 7 characters in the result from the end of the string i.e. Sujeet.

14 is the starting position of the string and 7 is the number of characters we want to find from the end of the string.

Note: In all 3 functions we have counted the number of characters and start position manually. We may count them using formula also. This will make our formula more robust and dynamic.

If you want to know how then please watch the video given below or click the below link to watch it on YouTube.

YouTube Link: https://youtu.be/hk1fqqpTIXw