MID in Excel

Mid function in excel is a type of text function which is used to find out strings and return them from any mid part of the excel, the arguments this formula takes is the text or the string itself and the start number or position of the string and the end position of the string to extract the result.

In simple words, MID Function in Excel is used to extract a small part of the string from the input string or return a required number of characters from text or string.

MID Formula in Excel

MID formula in excel has three compulsory parameters i.e. text, start_num, num_chars.

Compulsory Parameters:

text: it is text from which you want to extract the substring.
start_num: the starting position of the substring.
num_chars: the numbers of characters of the substring.

How to Use MID Function in Excel? (with Examples)

MID in Excel is very simple and easy to use. Let understand the working of MID function in Excel by some MID Formula example. The MID function can be used as a worksheet function and as a VBA function.

You can download this MID Function Excel Template here – MID Function Excel Template

MID in Excel as a worksheet function.

Example #1

In this MID Formula example we are fetching the substring from the given text string by using the MID formula in excel =MID(B3,2,5) it will fetch the substring from 2 characters and 5 letters from 2^nd position and output will be as shown in the second column.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #2

We can use the mid function the extract the first name and last name from the full name.

For First Name: here we have used the MID formula in excel =MID(B17,1,SEARCH(” “,B17,1)), In this MID Formula example, the MID function searches the string at B17 and start the substring from the first character, here search function fetches the location of space and return the integer value. In the end both function filter out the first name.

For the Last Name: similarly for the last name use the =MID(B17,SEARCH(” “,B17),100) and it will give you the last name from the full name.

Example #3

Suppose we have to find out the Block from ID which was located after “-“ in ID then use =MID(H3,FIND(“-“,H3)+1,2) MID formula in Excel to filter out the block ID two-character after “-“.

Example #4

We can also find out the domain name from the web URLs. Here is the mid function used to achieve this

=MID(H18,SEARCH(“://”,H18)+3,SEARCH(“/”,H18,SEARCH(“://”,H18)+3)-SEARCH(“://”,H18)-3)

Excel MID Function can be used as a VBA function.

Dim Midstring As String

Midstring = Application.worksheetfunction.mid(“Alphabet”, 5, 2)

Msgbox(Midstring) // Return the substring “ab” from string “Alphabet” in the message box.

The output will be “ab”.

Things to Remember About Excel MID Function

Excel MID formula returns empty text if the start_num is greater than the length of the string.

=MID(B3,21,5)= blank as the start number is greater than the string length so the output will be blank as shown below.

If start_num is < the length of text, but start_num + num_chars exceeds the length of string then MID returns the characters up to the end of text.

=MID(B3,1,15)

MID function excel through the #VALUE! Error
- If start_num < 1 ex. =MID(B3,-1,15)

- If num_chars is negative value.

=MID(B3,1,-15)