We use spreadsheets not only for number crunching but also for operating on the text-based data as well. There is a built-in support system to easily convert, trim and combine the strings. We know that we have formulas like LEFT and RIGHT that help us extract the substrings from the left and the right sides of a string respectively. What if we need to extract something in between? We can do so using the MID formula in Google Sheets.
MID(string, starting_at, extract_length)
- string – is the input text that we need to extract a substring from.
- starting_at – is the position from the left of string from which to begin extracting. We notate the first character of the string with position 1.
- extract_length – this specifies the number of characters the substring should have from the position defined by starting_at.
Usage: MID formula in Google Sheets
Nothing beats examples when it comes to understanding the concepts. So, let us dive right into them and reinforce what we’ve learned from the syntax. Have a look at the image below.
To understand these examples easily, think of the output values as a segment cut out from an input string. In the first example, the formula picked result “dom” from the 4th position of “Random text”, until it is 3 characters long. Similar is the case with the next two examples.
What if the formula reaches the end of the string even before it traverses the extract_length completely? The formula simply returns characters from starting_at to the end of the string. We demonstrated this in the final example. The total length of the string “Consistent” is 10. We wanted the formula to start at the 7th position, and extract 6 characters after that. That implies that the formula should traverse until 12th character, which doesn’t exist. So the formula just returned “tent”, because that is what is available until the end of the input string.