The MID function in Excel extracts a specific part of a text string using a starting position and length. The text can be entered directly in quotes or referenced from a cell, making it useful for retrieving substrings from data.
Syntax
=MID(Text to be sliced, starting index, length of the string)
Arguments
- The text to be sliced is the Original Text
- The Starting Index is the position of the First character that you want to extract.
- The length of the String is the number of characters to extract.
When dealing with real-life tasks in Excel, you will often find the MID Function handy when combined with other functions, as demonstrated in the following examples:
Steps to Extract First and Last Names
We already know how to get the first name using the LEFT Function and get the last name with the RIGHT Function. Below are some examples of extracting the values.
MID Formula to Get the First Name
To extract the first name from the full name in cell A2, you can use the following formula:
=MID(A2, 1, SEARCH(" ",A2)-1)
Note: The SEARCH function finds the position of the space in A2. Subtracting 1 avoids extra space, and MID extracts the text before the space (the first name).
MID Formula to Get the Last Name
Use the formula to Extract the last name from the cell:
=TRIM(MID(A2, SEARCH(" ",A2), LEN(A2)))
Note: SEARCH finds the space position, LEN(A2) sets the length, and TRIM removes any extra spaces from the extracted last name.
Example 1:
Here, the MID function is used on three cells:
- A2 (MAYA): MID(A2,1,2) → starts at position 1 and returns 2 characters → "MA".
- A3 (RAJ): MID(A3,2,1) → starts at position 2 and returns 1 character → "A".
- A4 (SHAUN): MID(A4,1,3) → starts at position 1 and returns 3 characters → "SHA".
The MID function can also be used on sentences, where spaces are counted as characters.
Example 2:
Here we applied the MID function to the A3 cell, which is a sentence, and we obtained a sliced string, which is between the A3 and the B3 cell. Now, let us see how the MID function was applied.
=MID(A3,5,14)
Here, the MID function extracts text from cell A3 starting at the 5th character "s" and returns 14 characters. Since spaces are also counted as characters, the result is "for geeks is t".
Steps to Get Substring Between Two Delimiters
To get a substring between 2 delimiters, we need to determine the positions of the two spaces in the original string. We can achieve this by using the SEARCH Function and some additional steps:
- Use the SEARCH Function to find the position of the first space (" ") in the text. and then add 1 to get the 'Start_num' argument for the MID formula.
- This ensured that we start extracting the characters that follow the first space.
SEARCH(" ", A2) + 1
- Find the position of the second space character by using nested SEARCH Functions.
- These nested functions instruct Excel to start searching for the second occurrence of the space character.
SEARCH(" ", A2, SEARCH(" ", A2) + 1)
- To determine the number of characters to return, subtract the position of the first space from the position of the second space and then subtract 1 from the result.
- This ensures that we don't include any extra space in the resulting substring.
SEARCH(" ",A2, SEARCH(" ",A2) +1) - SEARCH(" ",A2)
With all the arguments combined, here's the Excel MID Formula to extract the substring between the two space characters(i.e., the middle name):
=MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ",A2, SEARCH(" ",A2) +1) - SEARCH(" ",A2) - 1)
This formula extracts the middle name from A2. Similarly, you can modify it to extract text between any delimiters.
=MID(string, SEARCH(delimiter, string) + 1, SEARCH(delimiter, string , SEARCH(delimiter, string) + 1) - SEARCH(delimiter, String) - 1)
Extract Nth Word from Text String
In the below example, we will explore an inventive use of the MID formula in Excel, combined with several other functions, to extract the Nth word from a text string. The approach involves using the LEN, REPT, SUBSTITUTE, MID, and TRIM Functions to achieve the desired result.
The generic formula to extract the Nth word from a given text string is as follows:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))
- 'String' is the original text string from which you want to extract the desired word.
- 'N' is the number of words to be executed.
Pull a Word Containing a Specific Character(s)
Below is the formula that shows another non-trivial Excel Mid formula that pull a word containing a specific character(s) from anywhere in the original text string:
=TRIM(MID(SUBSTITUTE(string," ",REPT(" ",99)), MAX(1,FIND(char, SUBSTITUTE(string," ",REPT(" ",99)))-50),99))