The MID function in Google Sheets is useful to return a segment from the middle of any text string.
Table of Contents
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.
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.
MIDthis is our function. We need to add the attributes for it to work properly.
stringis the first attribute and it has to be the source string to extract a segment from.
starting_atis the position from the left side of the string from the extracting starts.
extract_lengthdefines the length of the segment to extract. It specifies the number of characters the substring should have from the position defined by
⚠️ Now a few notes before writing your own MID function in Google Sheets
- Keep in mind that the first character in any string has the index 1. If we’re specifying the
starting_atattribute, we should start counting the characters from 1.
- If the end of the string is reached before
extract_lengthcharacters are encountered, the
MIDfunction simply returns the characters from the position of
starting_atto the end of the string.
extract_lengthattribute must be a number value and must be greater than or equal to 1.
- Even if your original cell has a numeric value, still the
MIDfunction will extract the substring in string form. So it will become a textual value after the use of the
MIDfunction. You might convert that string back to a number in case this is not the behaviour you expect.
- You can return a segment of a string from start to end with the
MIDfunction, 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:
Here’s what this example does:
- Firstly, we made a cell active. This is where we wrote our
MIDformula. For the first example row, we selected the cell D2.
- Next, we started off our formula with an equal sign, followed by our function,
- 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_atvalue 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
MIDfunction 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.
- 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.
- Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘
MID, whichever works).
- 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!
- After the opening bracket ‘(‘, you have to add the right attributes. You can add cell references or direct values as well. Firstly, add the
stringattribute that is the original string you want to use for the function. For me, my
stringattribute is in cell A2, so I clicked on this cell to select it.
- After that, add a comma to separate the values and add the
starting_atattribute. 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.
- 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.
- 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.
- 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
MIDformula 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. 🙂