In our previous blogs, we had learned about how to use the excel LEFT formula to extract a specific number of characters from the beginning (i.e. from left). Similarly, the RIGHT function extracts specific characters from the end of the text (i.e. from the right). In this tutorial, we would learn about another such important function called the MID function in excel.
Akin to the above two functions, the MID formula in excel is also a useful function to extract a particular string from another text. However, unlike the above two formulas, the MID function extracts strings from between the text.
Let’s deep dive into this function and learn about how to use the MID function in Excel.
Here we go 😎
When to Use MID Function in Excel
The excel MID function is used to extract and get a particular number of characters from the middle of a text string.
This function allows you to specify-
- From which position should excel start extracting the text, and
- How many characters to extract. (covered in Syntax and Argument section below)
As the MID excel formula is a part of text function group, it returns a text string as its output.
Syntax and Arguments
=MID(text, start_num, num_chars)
Below bullet points explains the arguments of MID function:
- text – In this argument, specify the text from which you want to extract the text string. You may either manually enter the text (within double-quotes) or use a cell reference.
- start_num – In this argument, enter the position of the text string from which the MID excel function should start extracting the sub-string.
- num_chars – Specify the number of characters to extract starting from start_num.
Example of MID Excel Function
Let us now understand how the MID excel function works in excel with the help of some practical examples.
The below image shows some texts in column A, which consists of four numbers and two alphabets.
Suppose you want to extract the alphabet characters from the text strings. To achieve this, simply use the below formula in cell B2:
As a result, the excel would return the extracted text as ‘AB’.
Copy this formula to other cells in column B, as shown below:
Explanation – In the first argument, we have used the cell reference A2, which is the original text. The value in the second argument (viz. 3) denotes that the formula shall start extracting from character number 3 (viz. from A). The value in the third argument (viz. 2) tells Excel to extract 2 characters from the starting position. Quite simple and easy formula 😉
Error Handling in MID Excel Function
As we can understand from the MID formula example above, it is not a difficult function to use. However, some common mistakes made by an excel user, make this formula seem difficult. Let’s see how to avoid errors in this excel formula:
- The MID function will return #VALUE! error, when start_num value is less than 1.
- The MID function also gives #VALUE! error, when num_chars value is less than zero (a negative value).
- The MID Function will return an empty string or blank cell, when the num_chars value is put as zero (0).
- Similarly, the MID function will return an entry string, when the start_num value is greater than the total length of the text.
In the below example, the text “excelunlocked” has a length of 13. However, we have put the start_num argument value as 40 which is out of range.
Thank You 🙂