How to Use MID Function in Google Sheets

The MID function in Google Sheets is useful to return a segment from the middle of any text string.

The MID function is a text function that is used to extract a special middle part of a string. The MID stands for ‘middle’.

The extracted substring (the portion of text) consists of a given number of characters beginning at a position of our choosing.

Let’s take an example.

Say you have a cell with lengthy content and you want to get a nine-character long portion of that text value starting from the fourth character.

So how do we do that?

It’s simple. The MID function just needs an input string (a textual value), a starting point and the length of the substring we want to extract.

The MID function will automatically output a certain portion or segment of the string.

Let’s go straight into some real-business examples where we will deal with actual values and textual strings and subsequently apply our understanding of MID function in Google Sheets.

The Anatomy of the MID Function

The syntax (the way we write) the MID function is as follows:

=MID(string, starting_at, extract_length)

Let’s break this down to understand better what each terminology means:

• = the equal sign is how we start every function written in Google Sheets.
• MID this is our function. We need to add the attributes for it to work properly.
• string is the first attribute and it has to be the source string to extract a segment from.
• starting_at is the position from the left side of the string from the extracting starts.
• extract_length defines the length of the segment to extract. It specifies the number of characters the substring should have from the position defined by starting_at.

⚠️ Now a few notes before writing your own MID function in Google Sheets

1. Keep in mind that the first character in any string has the index 1. If we’re specifying the starting_at attribute, we should start counting the characters from 1.
2. If the end of the string is reached before extract_length characters are encountered, the MID function simply returns the characters from the position of starting_at to the end of the string.
3. The extract_length attribute must be a number value and must be greater than or equal to 1.
4. Even if your original cell has a numeric value, still the MID function will extract the substring in string form. So it will become a textual value after the use of the MID function. You might convert that string back to a number in case this is not the behaviour you expect.
5. You can return a segment of a string from start to end with the MID function, but Google Sheets has some special text functions for these edge cases. The RIGHT function returns a substring from the right side (the ending) of the string, while the LEFT function does the same from the left side (the beginning) of the string.

A Real Example of Using MID Function

The below examples show how MID function is used in Google Sheets.

The example above shows exactly how the MID function works to get the substrings of several strings.

In each row, we have an original string, defined the starting point and the length of the substring we want to extract. The formula outputs the segments from the middle of the strings.

The function of cell D2 is as follows:

=MID(A2,B2,C2)

Here’s what this example does:

• Firstly, we made a cell active. This is where we wrote our MID formula. For the first example row, we selected the cell D2.
• Next, we started off our formula with an equal sign, followed by our function, MID.
• After that, we added our values into it. We used cell references for all of the attributes. The first attribute is string, which we have in cell A2 containing the text ‘Break the ice‘ in the first example.
• Then, we have the starting_at value in cell B2, which is 4. As shown above, this means that our substring should start from the 4th character of the original string. This is the letter ‘a‘ in our first example.
• After that, the last attribute we had to add was extract_length. So we referenced the cell C2 that has the value 9.
• As a result, we got ‘ak The Ic‘ which is the nine-character long substring of the original string starting from the fourth character.
• Lastly, we applied the same function with the corresponding cell references to the whole column to execute the same formula on every string in the list.
• Note what happens in the 6th row. Despite our original cell content being a numeric value in cell A6, the MID function returns the substring as a textual value.

It’s so easy, right?

Go ahead and give it a shot! Using the link below you can make a copy of the spreadsheet:

How to Use MID Function in Google Sheets

Let’s begin writing our own MID function in Google Sheets.

1. To start, click on the cell where you want to show your result. For this guide, I will be selecting D2, where I extracted my first substring.

2. Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘mid‘ (or MID, whichever works).

3. Now you can see that the auto-suggest box will pop-up with the name of the functions that start with MID. There are more of them, so make sure to select the right one!

4. After the opening bracket ‘(‘, you have to add the right attributes. You can add cell references or direct values as well. Firstly, add the string attribute that is the original string you want to use for the function. For me, my string attribute is in cell A2, so I clicked on this cell to select it.

5. After that, add a comma to separate the values and add the starting_at attribute. Again, you can add an exact number, or you can reference another cell that has your number. I will be clicking on cell B2 that contains 4 as my index from which I want to start my substring.

6. The next and last attribute is extract_length. Highlight the cell containing this value or simply write the number you need as length. For my example, I will be selecting the cell C2 that contains the number 9. You might even see the preview of the result at this point as you can see in my example.

7. Finally, hit the Enter key to close the brackets and get the result. Great! You should see the substring of your desired length, starting from the character that you defined.

8. Now if you want to apply the same formula to the other rows of your list, simply drag down the function by clicking on the little blue square in the bottom right corner of the cell. This will apply the same MID formula to your rows, but with the corresponding cell references.

That’s it! You can now use the MID function together with the various other Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

Subscribe