MID Excel Function

Article byTanuj Kumar
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

MID in Excel

The MID function in Excel is a text function used to find out strings and return them from any middle part of the Excel. This formula extracts the result from the text or the string itself, the start number or position, and the string’s end position.

For example, suppose we have a table of products from A2:A5: “Dolls,” “Barbie,” “Bottle,” and “Pen.” in the worksheet. We need to fetch the substring from the given substring. When we type,  =MID(A2,2,4) returns “oll.” It will bring the substring from 2 characters and 4 letters from the 2nd position.

In simple words, the MID function in Excel extracts a small part of the string from the input string or returns a required number of characters from text or string.

MID Formula in Excel

MID Formula

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

Compulsory Parameters:

  • text: It is a 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

MID Excel Function Explained in Video

 

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

The MID function in Excel is very simple and easy to use. Let us understand the working of the MID function in Excel by some MID formula examples. The MID function can be used as a worksheet function and 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 using the MID formula in excel =MID(B3,2,5). It will bring the substring from 2 characters and 5 letters from the 2nd position, and the output will be as shown in the second column.

MID Function Example 1

Example #2

We can use the mid function that extracts the first and last names from the full names.

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 starts the substring from the first character, here search function fetches the location of space and return the integer value. In the end, both functions filter out the first name.

MID Function Example 2

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.

MID Function Example 2-1

Example #3

Suppose we have to find out the block from ID, which is 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.

MID Function Example 3

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)

MID Function Example 4

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 empty, as shown below.

MID Function Example 4-1
  • If start_num is < the length of text, but start_num + num_chars exceeds the length of the string, then MID returns the characters up to the end of the text.

=MID(B3,1,15)

MID Function Example 4-2
MID Function Example 4-3
  • If num_chars is a negative value.

=MID(B3,1,-15)

MID Function Example 4-4

Recommended Articles

This article is a guide to MID Function in Excel. We discuss the MID formula in Excel and how to use the MID Excel formula along with the MID formula example and downloadable Excel templates. You may also look at these useful functions in Excel: –