MID Excel Function

What Is MID Function In Excel?

The MID Excel function returns a specified number of characters from the middle of a given text string. Also, the MID function can be used with VALUE, SUM, COUNT, DATE, DAY, and other functions.

The MID Excel function is available under the Excel TEXT functions group.

For example, the below table shows the value in cell A2. Now, we need to apply MID function to detect the output.

MID function in Excel

The steps used to apply MID excel function are as follows:

Step 1: To begin with, select the cell to display the output. In our example, the cell is B2.

Step 2: Next, enter the complete formula =MID(A2,4,3)

Step 3: Then, press Enter key.

The function has returned the output as shown in the below image.

MID function in Excel -1

Likewise, we can apply MID function to obtain the middle value.

Key Takeaways
  • The syntax of the MID excel function is =MID(text,start_num,num_chars) where,
    • text denotes the original text string.
    • start_num indicates the integer that specifies the position of the first character we want to be returned.
    • num_chars shows the number of characters, starting with start_num, to be returned from the start of the text. It is the number of characters to be extracted.
  • If the user does not specify the last parameter of the MID function, it will take 0 by default.
  • The number formatting is not the part of a string and cannot be extracted or counted.

MID() Excel Formula

The syntax of the MID excel formula is:

MID function syntax

where,

  • text: It denotes the original text string.
  • start_num: It indicates the integer specifying the position of the first character we want to be returned.
  • num_chars: It shows the number of characters, starting with start_num, to be returned from the start of the text. It is the number of characters to be extracted.

Unlike other functions, all the arguments are mandatory in MID excel formula.

How To Use MID Excel Function? (With Steps)

#1 – Access From The Excel Ribbon

  1. Choose the empty cell to display the result.
  2. Go to the Formulas tab.
  3. Select Text from the menu.
  4. Select MID from the drop-down menu.
  5. The Function Arguments window appears.
  6. Based on the number of arguments, enter the value in the Text, Start_num, and Num_chars. 
  7. Select OK.

#2 – Enter The Worksheet Manually

  1. Select an empty cell for the output.
  2. Type =MID( in the selected cell. Alternatively, type =M and double-click the MID function from the list of suggestions shown by Excel.
  3. Enter the arguments as cell references or direct values.
  4. Close the parenthesis and press the Enter key.

Let us take a basic example to understand this function. The following table shows the strings in column A. Now, we need to extract the text from the string using MID excel function.

How to use MID function in Excel

The steps to extract the text from the string using MID Excel function are as follows:

  1. To begin with, select the column to display the result. The selected column, in this case, is column B.

  2. Next, enter the MID excel function formula in cell B2.

    Select the cell A2 in the text argument.


    Enter the value as 10 in the start_num argument.



    Enter the value ‘4’ in the num_chars argument.



    So, the complete formula is =MID(A2,10,4).

  3. Then, press Enter key.

    We can see that the function has returned ‘boat’ in cell B2.
    How to use - Step3

  4. Drag the cursor to cell B5 using AutoFill option.

    How to use - Step4

    Likewise, we can use MID function to extract mid values.

Examples

Example #1 – Extracts The First And Last Names From The Full Names

The below table shows the full name of various people in column A. We need to extract the first name and the last name from the given table array using MID Excel function.

MID function in Excel Example1

The steps to extract the text from the string by the MID in Excel are as follows:

Step 1: To begin with, select the column to display the cell. In this example, we will enter the First name in column B and the Last name in column C.  

Step 2: Next, enter the MID formula in cell B2 for extracting the first name.

Step 3: Now, enter the value for the text argument as A2.

Example1 - Step3

Step 4: The value entered for the start_num argument is 1.

Example1 - Step4

Step 5: The value entered for num_chars argument is the SEARCH formula as we extract the first name.

Example1 - Step5

Step 6: The entered complete formula in cell B2 for extracting the first name is =MID(A2,1,SEARCH(“ ”,A2)-1)

Press Enter key.

Example1 - Step6

Step 7: The result is returned as Tom.

Next, drag the cursor to cell B5; all the first name from the data is extracted in column B as shown in the following image.

Example1 - Step7

Step 8: Next, enter the MID formula in cell C2 for extracting the Last name.

Step 9: Firstly, we will enter the TRIM excel formula, and then the MID formula value entered for the text argument is A2.

Example1 - Step9

Step 10: Now, enter the value for the start_num argument and the SEARCH formula for extracting the last name.

Example1 - Step10

Step 11: The value entered for the num_chars argument is the LEN excel formula as we are extracting the Last name.

Example1 - Step11

Step 12: So, the entered complete formula in cell C2 for extracting the last name is =TRIM(MID(A2,SEARCH(“ ”,A2),LEN(A2)))

Press Enter key.

The result is returned as Cruise.

Example1 - Step12

Step 13: Drag the cursor to cell C5;

Example1 - Step13

Likewise, we can use MID Function to extract values.

Example #2 – Find Out The Domain Name From The Web URLs

The below table shows the Web URLs in column A. We need to use the following steps to find the domain name from the table array using MID Excel Function.

MID function in Excel Example 2

The steps to extract the text from the string by the MID in Excel are as follows:

Step 1: To begin with, select the column where we will enter the formula and calculate the result. The selected column, in this case, is column B.

Step 2: Next, enter the MID Excel formula in cell B2 for extracting the domain name.

Step 3: The complete formula in cell B2 for extracting the domain name is =MID(A2,SEARCH(“://”,A2)+3,SEARCH(“/”,A2,SEARCH(“://”,A2)+3)-SEARCH(“://”,A2)-3).

Example2 - Step3a

Step 4: The result is returned as www.excelmojo.com in cell B2. 

Example2 - Step4

Step 5: Now, drag the cursor to cell B5; all the domain name from the data is extracted in column B.

Example2 - Step5

Similarly, we can use MID function to find the values.

Example #3 – Extracting Last n Digits From Some Numbers

The following table shows numbers in column A. We need to extract the last n digits from the given data using MID Excel function.

MID function in Excel Example 3

The steps to extract the text from the string by the MID Excel function are as follows:

Step 1: First, select the column where we will enter the formula and calculate the result. The selected column, in this case, is column B.

Step 2: Next, enter the MID formula in cell B2 for extracting the Last n Digits.

Step 3: So, the complete formula in cell B2 for extracting the Last n Digits is =MID(A2,LEN(A2)-2+1,2).

Example3 - Step3

Step 4: The function has returned the result as 34 in cell B2. 

Example3 - Step4

Step 5: Next, press Enter and drag the cursor to cell B5;

Example3 - Step5

Likewise, we can use MID excel function to obtain the values.

Example #4 – MID Function With Arrays

The following table shows input values of fruits in column A. We need to extract the text from the table array using MID Excel function.

MID function in Excel Example 4

The steps to extract the text from the string by the MID in Excel are as follows:

Step 1: First, select the column where we will enter the formula and calculate the result. The selected column, in this case, is column B.

Step 2: Next, enter the MID formula in cell B2 for extracting the data from the array.

Step 3: Then, enter the value for the text argument as A2:A5.

Example4 - Step3

Step 4: Similarly, enter the value for the start_num argument as 1.

Example4 - Step4

Step 5: The value entered for the num_chars argument is the SEARCH formula as we extract the text.

Step 6: The entered complete formula in cell B2 for extracting is =MID(A2:A5,1,SEARCH(“ ”,A2:A5)).

Example4 - Step6

We can see that the function has returned the value in B2.

Example4 - Step6a

Step 7: Drag the cursor to cell B5;

Example4 - Step7

Similarly, we can use MID Excel function to find values.

Things To Remember

  • The MID Excel function returns the middle or central characters in the data.
  • All the arguments in MID Excel formula are mandatory.
  • The function extracts the characters from any side of a specified text.
  • It extracts text from inside a text string based on location and length.
  • The MID function returns empty text if the start_num is greater than the length of a string.
  • The “#VALUE!” error occurs if the value of the num_chars argument is less than 0 or if the value of the start_num argument is less than 1.

Frequently Asked Questions (FAQs)

1. What does the MID function do in Excel?

The MID function extracts the substring from the main string.
The syntax of MID is
=MID(text,start_num,num_chars)
For example, the below table shows the value in cell A2. We need to apply MID Excel function to detect the output.

MID function in Excel FAQ 1

The MID formula is entered in cell B2; the complete formula is =MID(A2,4,3),and the result is returned as 456. 

FAQ1-1

Likewise, we can apply MID Excel function to obtain values.

2. When to use the MID Excel function?

The MID Excel function is used when we want to extract a small amount of data from huge data. That is when we want to fetch the text or number from a string or bunch of numbers.

3. Why does the MID function not work in Excel?

The MID function does not work in Excel due to two reasons;
1. If the value is equal to ‘0,’ the MID function will return a blank cell.
2. If the value is less than ‘0,’ the MID function will return “#VALUE!”.

Download Template

This article must help understand MID in Excel with its formula and examples. You can download the template here to use it instantly.

This has been a guide to MID Excel Function. Here we learn how MID() works with SEARCH, TRIM and LEN functions, examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.