Mid formula in excel is a text formula which is used to find out a string or a substring in a given combination of strings, this function takes three arguments first is the text we want to extract and the second argument is the start position of the string while the third and last argument is the length of the string.
MID Formula in Excel (Table of Contents)
Excel MID Formula
The MID formula is an excel function which pulls out a given number of characters from the given starting point in a given string.
General Syntax for MID formula in excel is as follows:
Below is the explanation for MID Function syntax in detail:
- String: String identifies as the source string provided to the function.
- Start_Num: Start Num is the number provided to the function from where, to begin with.
- Num_Chars: Num Chars is the number of characters which needs to be pulled out from the string.
For example, if the given string is Computer and we want to find the three of the characters in the given string from the second position of the string. The formula for the above statement would be like
=Mid ( “A2”, 2, 3 ) and we will get the result as “nan”. As from the second position, character n starts and the given three characters in the series are “an” so the result is “nan”.
How to Use MID Formula in Excel? (with Examples)
There are various uses of Mid formula in Excel which we will learn through various examples.
First, let us know the basic use of excel MID formula. We will have a set of names and we will try to find the 2 characters from the second position from the string. Have a look at the below data,
Step 1: In the cell, B2 write the following excel formula = Mid(
Step 2: In the text section, select Cell A2,
Step 3: Now as we know we will take the start number as 2 so write 2,
Step 4: The number of characters required is also 2, so we again write two.
Step 5: Close the parenthesis and Press Enter to see the result.
Step 6: Now drag the formula to cell B6 for the rest of the names.
What Mid function did, in this case, is that it found out the character from the starting number provided to it, then it extracted those character up to which the number was provided.
There are other uses of Mid formula in excel like finding a middle character in a given string. We will take the previous data set as our example to find out the middle name. But we use full names this time to find out the middle name from the Mid and If nested function.
Have a look at the data below first,
Here we have full names of people and we need to extract the middle name of the people.
Step 1: In cell B2 write the following formula,
Step 2: Press enter to see the result.
Step 3: Now drag the formula to cell B6 to see the final result.
Now it is time to explain how this formula works. The Find Formula Search for the space between the characters and Mid function treats the character after the first space as the first character and the character before second space as the total number of space, and when space is found it displays the middle name as the result.
What we did with Find and If function in the above example we can do the similar with the Search function. The search function also works in a similar manner as the find function. For demonstration, we will use the same data set from example 2 and nest Mid Function with the search function to find the result.
Have a look at the below data,
Step 1: In cell B2, write the following Mid excel formula,
Step 2: Press Enter and see the result.
Step 3: Now we drag the formula to cell B6 and see the final result.
The search function also works on the same concept of Mid Formula in excel
Explanation Of MID Formula in Excel
As explained above excel Mid Formula is used to find out the specific number of characters from a given substring.
We provide formula three basic things, One is the substring itself the second is the starting character and the third is the number of characters we are trying to show as output.
Things To Remember
There are a few basic things we need to remember about Mid formula in excel:
- The mid function finds the characters from the starting point provided to it.
- It Finds only the number of characters we input in the formula.
- If the string has spaces in it we need to nest the Mid formula by Find or Search Function.
This has been a guide to MID Formula in Excel. Here we discuss how to use MID formula in Excel along with some examples and downloadable excel template. You may learn more about excel from the following articles –