Open In App

Excel MID Function

Improve
Improve
Like Article
Like
Save
Share
Report

If you use programming languages, like Python, you must have used a function called string slicing, which extracts a part of the string and can directly print it, or store a result. The same functionality can be achieved in Excel when we use Excel’s MID function.

The MID function can return a part of the string provided to it. Let us see the workings of this function.

Excel MID Function Syntax

This function can be used to slice a given string and return only a part of that string. It considers a starting point, which will be the starting index of the string, from which we want to print. It will consist of the string that we want to slice. It can be a string provided in double quotes(” “), or it can be a reference to a cell on which slicing is to be done. It will also consist of the length of the characters, which will indicate how many characters we want to print taking reference from the starting index.

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.

Excel MID Function Examples

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:

How 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 scans the original string (A2) for the space character(” “) and returns its position. By subtracting 1, we avoid capturing trailing spaces. Then the ‘MID’ Function extracts a substring from the first character and spans up to the character preceding the space, effectively fetching 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: The ‘SEARCH’ Function is used to determine the starting position (the space character). Since we don’t need to specify the exact end position, we can use ‘LEN(A2)’ to represent the total length of the original string. The ‘TRIM’ Function is then used to remove any extra spaces that might appear before or after the extracted last name.

Example:

Dataset

Here, we used the MID function 3 times on cells, i.e. on MAYA, RAJ, and SHAUN. Now, we will see individual 3 formulas for these three:

  1. In cell A2(MAYA), we applied–> MID(A2,1,2): Here, we used A2 as our text to be sliced. We will start from the 1st index, ie the very first element, which is “M”.The length of the string is kept as 2, so the string will be sliced till the 2nd index of the string, which is “A”. This will result in the output “MA”.
  2. In cell A3(RAJ), we applied–> MID(A3,2,1): Here, we used A3 as our text to be sliced. We will start from the 2nd index, which is “A”.The length of the string is kept as 1, so the sliced string will be till this index only. This will result in the output “A”.
  3.  In cell A4(SHAUN), we applied–> MID(A4,1,3):  Here, we used A4 as our text to be sliced. We will start from the 1st index, ie the very first element, which is “S”.The length of the string is kept at 3, so the string will be sliced till the 3rd index of the string, which is “A”.  This will result in the output “SHA”.

It is not necessary to provide just a word to be sliced, we can also use a sentence, that can be sliced by using the MID function. The only thing to keep in mind is that the spaces between the words in a sentence will also be counted as an index. 

Example 2:

Using- MID-Function

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 will slice the string stored in the A3 column. The starting point of slicing is from the 5th index, which is “s”.Now, as previously discussed, the spaces between the words in a sentence, also count as an index, so the length of 14 also included the space between “s” and “t”. Therefore the outcome is “for geeks is t”.

How to get Substring Between 2 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)+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)

Using this formula will efficiently extract the middle name from cell A2, even if it is present in the first and last names.

Similarly, you can extract substring between any other delimiters by adjusting the formula accordingly:

=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:

TRIN(MID(SUBSTITUTE(string, ” ‘, REPT(” “,LEN(string))), (n-1) * LEN(string) + 1, LEN(string)))

‘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 pulla 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))

FAQs on MID Function in Excel

What is the MID Function in Excel?

MID Function helps you to get a specified number of characters from a text string based on a given starting position.

What is the Syntax of the MID Function?

The Syntax of the MID Function is as follows:

=MID(text, start_num, num_chars)

text is the original text

Start_num is the position of the First character that you want to extract.

chars is the number of characters to extract.

How to handle errors with the MID Function?

You should be cautious with the ‘start_num’ and ‘num_chars’ arguments to avoid errors. If ‘start_num’ is less than 1 or num_chars’ is negative, the MID Function will return the #VALUE! error.. Also, make sure that ‘start_num’+’num_chars’ does not exceed the length of the string.

Is the MID Function case sensitive?

Yes, the MID function is case-sensitive. It will extract characters exactly as they appear in the text string.



Last Updated : 12 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads