MID function - The Replacement of Nested LEN LEFT and RIGHT function step by step

Hi Friends,

In my last article I discussed about the function LEN() LEFT() and RIGHT() step by step by the help of  two basic Excel examples. In this article I'll show you another new function MID() and shall show you how beautifully this function can be used to get the data from the middle of a cell. 

This will replace the use of nested LEFT and RIGHT function.

Description

The Microsoft Excel MID function extracts a substring from a string , starting at any position and ending at given position.

The MID function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

The syntax for the MID function in Microsoft Excel is simple, consists of three parts :

MID( text, start_num, num_chars )

Parameters or Arguments

text : The string or text in a cell that you wish to extract from.

num_chars : The position in the string that you will start extracting from. The first position in the string is 1. Remember it is not Zero.

num_chars : The number of characters that you wish to extract.

For Example : If E1481210045089M52 is my given cell content and suppose I need data starting from third position to seventh position then this number would be 4812100.

The use of MID function in excel is very simple for the above example it is =MID(B2,3,7) as shown in the below image.

You type '=' sign type MID and press tab to get first bracket then select the cell from which you want your data and put comma type the starting number from which you want to count and then again put a comma and give the ending number. This will give you data from the middle of a cell's data.

How to use MID Function in Excel

We can use LEFT and RIGHT (with LEN for more dynamic  and also without LEN function) functions in nested conditions. This requires a simple mathematics and you need to count the characters and put in the formula. So, lets' do it.

First we need to use LEFT function to get the data upto 9th position, then we have to use the RIGHT function starting from right side upto 7th position. The formula now becomes =RIGHT(LEFT(B2,9),7)

But the benefit of MID function is that it can easily replace the use of nested LEFT and RIGHT function in some cases. MID function allows us to select specific data from a cell by providing starting number and ending number.

How to use MID LEN LEFT and RIGHT Function in Excel

Example : Use of LEN() with LEFT() function and use of LEN function with RIGHT function.

In this example I shall show you how to use LEN with LEFT and LEN with RIGHT function. As you can see from the below image I have two tables two columns, one column is common Product Code. If you look carefully then you can notice that every product code is either ending by '- and text' or 'text and -', we need to get the data but without '-text' or 'text-', we shall use LEN with LEFT and LEN with RIGHT to get the desired data.

Use of LEFT() and LEN() : My data in A2 cell is E148121009M52-TEST, I need to remove -TEST  and print this in B2 cell, lets' do it.

We all know that Syntax of LEFT is =LEFT(text,[num_chars]),  so write =LEFT and hit TAB key in your keyboard which will create an opening bracket and then select A2 i.e., text parameter. Now for the second parameter [num_chars] we'll use LEN function, write LEN(A2), and as we can count that the text after the characters E148121009M52 is of five characters so we need to deduct five from the total length of the string in A2.

Our formula now becomes, =LEFT(A2,LEN(A2)-5) and this will return the desired data without '-TEST'

Use of LEFT() and LEN() : The process is same as the previous example, the only difference is that the text in D2 i.e., TEST-E148121009M52 has extra characters 'TEST-' in the beginning of the data in D2. If you write this formula it will be like =RIGHT(D2,LEN(D2)-5)  and this will remove the five characters in the beginning of this text.

LEN with LEFT and RIGHT Function in Excel

You need to know how to use LEFT and RIGHT function works because sometime MID will not be applicable in some situations, the option will be to use either LEFT or RIGHT function with or without LEN function.

Thank you for reading...