How to Use the Function MID in Excel

The function MID can allow us to extract characters from the middle of a given string of values. Once we give the function a starting position and specific length, it will show us the values we need. This is useful in many different applications, so let’s learn how to use it.

In example 1 you see below, we have 4 different text strings and each of them includes a name, a salary amount, and a job position. What we need to extract here is the salary figure, which is in the middle of the text string. So, let’s get started.

Example 1 – The Basics Basics

Firstly, let’s go to cell C3 and insert the function MID so you can follow these steps:

1. Now, let’s add our first argument, which is “text”. For this one, select cell B3, which is where our text string is located.

2. Next, we have the argument “start number”, which refers to the starting position of what we want to extract. This is always considered from left to right. As you can see below, the name John has 4 letters. Then, the dash symbol is the 5th character in the text string. Therefore, the first character of the salary figure we want to extract is in the 6th position. So, let’s type 6 for this argument.

1. Finally, we have the argument “number of characters” in the function MID, which represents how many characters we want to extract. In this case, John has a 5-figure salary, so let’s type 5 for this argument.

2. To conclude, let’s close the brackets and press ENTER. The result is 35,000, which is John’s salary.

3. Now, all the names in this example have 4 characters and all the salaries are 5 figures. This means we can expand the function to the rest of the array to get all the other results.

Now we can move on to example 2, where things get a little different.

Example 2 Function MID – “Start Number” Argument

In example 2, we have another set of text strings with a name, a bonus figure, and a job position. Right off the bat, you can see that the names have different lengths. That means that the function we just created won’t work here:

1. Firstly, copy the function MID from example 1 to cell C9.

2. Lastly, press ENTER.

As you can see, the function couldn’t extract the figure. Therefore, we have to make some adjustments.

Creating the Function FIND

In this example, the dash symbol will serve as our reference point. Now, the first thing we need to do here is find the position of the dash within the text string. That’s where the function FIND comes in, so follow these steps:

1. Firstly, insert the function FIND in cell E9.

2. Secondly, type the dash within quotation marks (“-”) as our “find text” argument so the function can identify it.

3. Following that, select cell B9 as our “within text” argument, which is where the text string is.

4. Lastly, the argument “start number” is 1. This indicates that we want Excel to look for the dash symbol from the beginning of our text string.

5. Finally, close brackets, press ENTER, and the result is 9.

This result means that the dash is the 9th character in our text string. Now, we can use this information to adjust our function MID.

Adjusting the Function MID

Go to the MID function and then follow these steps:

1. Firstly, replace the number 6 for the “start number” argument with the number 10. Why 10 instead of 9? Because 9 is the position of the dash. But the figure we want to extract starts in the 10th position.

2. Finally, press ENTER and we get the correct extraction as you can see below.

However, we want to make the function dynamic. To do that:

1. Firstly, go back to the function FIND we created above and copy it after the equal sign.

2. Next, go to the function MID and replace the number 10 in our “start number” argument with the function FIND.

3. Then, type +1 after the function FIND so it can always find the character after the dash.

4. Lastly, press ENTER and expand the function to the rest of the rows.

As you can see in the image above, it successfully extracted all the figures we needed because we made it dynamic.

Example 3 Function MID – “Start Number” & “Number of Characters” Arguments

In the last example for this tutorial, we have another set of text strings with the same elements. But this time, both the names and the bonus figures have different lengths as you can see below.

Let’s try again what we did in example 1:

1. As we did before, copy the function from example 1 and paste it into cell C15.

2. Lastly, press ENTER.

As you can see in the image above, the function MID didn’t extract the bonus figures. This means we have to make adjustments again. This time, we have to use two reference points: the dash and the slash.

Finding the Position of the Dash and the Slash

To determine the position of the dash and the slash, we just have to use the function FIND again:

1. Firstly, go to cell E15 and insert the function FIND.

2. Just like last time, type the dash within quotation marks (“-”) as our “find text” argument.

3. Thirdly, select cell B15 for the “within text” argument.

4. Lastly, type 1 as our “start number” argument.

5. Close brackets, press ENTER, and we get 9 again.

Then, let’s find the slash:

1. Go to cell F15 and insert the function FIND again.

2. This time, the “find text” argument will be the slash within quotation marks (“/”).

3. Thirdly, select cell B15 as our “within text” argument.

4. Finally, type 1 as our “start number” argument again.

5. Close brackets, press ENTER, and the result is 14.

To summarize, we now know that the dash symbol is the 9th character and the slash symbol is the 14th character in the text string. Next, we have to determine the difference between the two. So, go to any cell and subtract cell F15 minus cell E14. The result will be 5.

Now, we can use this to create a nested formula.

Nesting the Functions FIND

To nest the two FIND functions and MID, follow these steps:

1. Firstly, go to the function FIND for the dash and copy it.

2. Secondly, copy it to replace E15 in our subtraction.

3. Thirdly, do the same with the function FIND for the slash. This time, replace F15.

4. Finally, press ENTER and the result will still be 5.

Adjusting the Function MID

It’s important to note here that the issue with this calculation is that it considers one of the extremes. In this case, the slash symbol is also included in the result. That’s why it’s 5 instead of 4, which is how many numbers we actually have in our text string. With that in mind, we need to adjust our function MID:

1. Firstly, go to the function FIND for the dash and copy it.

2. Secondly, paste it as our “start number” argument in the function MID. Remember to type +1 because the bonus figure starts after the dash.

3. Thirdly, copy the nested formula we just created to calculate the difference.

4. Then, paste it as our “number or characters” argument.

5. Next, type -1 right after the pasted formula because the figure we want to extract ends before the slash. So, we have to exclude that extreme.

6. Lastly, press ENTER, and we get the correct figure. You can then delete the calculations we made on the side and extend the formula to the rest of the rows.

Because we have created a dynamic formula, we can now make any changes to the text string and still get the correct extraction. For example, we can change the name PETER in the second text string to PETERSON and our formula will still show us the correct results.

Additionally, if we remove or add numbers to the figure, the final result will be automatically adjusted.

That’s it! Now you can easily extract specific values you need from any given string of data in Excel.