In this article we will show you how to extract a Google Sheet substring in just a few simple steps. We cover using the LEFT MID and RIGHT functions.
=LEFT(string, length)
Where
String is the either the string itself enclosed in double quotes or the reference to the cell containing the string
Length is the number of characters to extract, starting from the beginning of the string. You can store the value inside the function or set a reference to another cell
Example: we want to extract the country + area code of the following numbers:
The country + area code of the telephone numbers cover the first six characters of each string.
Thus, we set the length as 6. The strings of telephone numbers are all along Column A. For the first number, the string is in A2; for the second number, the string is in A3; etc.
We can then set up the formula for the first number along Row 2 as
=LEFT(A2,6)
And input it to cell B2.
Besides typing the whole formula, another way of setting it up is through the following steps:
The country plus area code has been extracted. For the first number +1-213-555-115 the result is +1-213. You can copy the formula to other entries to see their country+area codes as well.
=MID(string,start_loc,length)
Where
String is the either the string itself enclosed in double quotes or the reference to the cell containing the string
Length is the number of characters to extract, starting from the position specified at start_loc. You can store the value inside the function or set a reference to another cell
Char_num is the number of characters to extract, starting from the position specified at start_loc
Example: we want to extract just the area code of the following numbers:
The area code has three characters; thus, the length is 3. You count the starting location as the first character that is part of the substring that you want to extract.
For the given phone numbers, the area code starts at character 4; thus, start_loc is 4. Finally, we can just point the string to the cell containing it. For Row 2 that’s A2.
With the given values, we then set up the formula for Row 2 as
=MID(A2,4,3)
And input it to cell B2.
Alternatively, you can do the following steps:
The area code has been extracted. For the first number +1-213-555-115 the result is 213. You can copy the formula to other entries to see their area codes as well.
=RIGHT(string, length)
Where
String is the either the string itself enclosed in double quotes or the reference to the cell containing the string
Length is the number of characters to extract, starting from the end of the string. You can store the value inside the function or set a reference to another cell
Example: we want to extract the last three digits of the following numbers:
Since we want the last three digits, we set the length to 3. The strings of telephone numbers are all along Column A. For the first number, the string is in A2; for the second number, the string is in A3; etc.
We can then set up the formula for the first number along Row 2 as
=RIGHT(A2,3)
And input it to cell B2.
Besides typing the whole formula, another way of setting it up is through the following steps:
The last three digits have been extracted. You can copy the formula to other rows to see the last three digits of other listed numbers.
The three previous examples that we have require you to know the exact length of the string and the position of the substrings you need.
What if you don’t know any of these but you instead know the general format of the string and therefore know where to extract the substring relative to a character, a word, or a phrase?
Fortunately, the solution is simple! We just combine LEFT and RIGHT with either the FIND or SEARCH functions.
For the next two examples, we will combine LEFT and RIGHT functions with FIND.
=LEFT(string, FIND(text, string)-1)
Where:
LEFT function - tells Google Sheets to extract all leftmost characters from the string up to a certain position
String - where you want to extract substring
Text - the text that tells Google Sheets where to stop the extract process. If added to the formula, you should enclose it in double quotes
FIND function - tells Google Sheets where a specific text is located along a string. The output is the location of the specific text.
If you check the formula, we added a -1 after the FIND function. If we don’t include the -1 to the formula, the output will include the text you specify. Including the -1 means only the string before the text is included.
This formula tells Google Sheets to do the following:
Example: We want to extract the surnames from the following list of names:
The names are all located along Column A. For Row 2, the string is therefore stored in A2 and we will reference this in our formula. The text then is the comma symbol that separates the surname from the first name.
With the given values, we then set up the formula for Row 2 as
=LEFT(A2, FIND(“,”, A2)-1)
And input it to cell B2.
Alternatively, you can do the following steps:
The cell B2 now contains the extracted surname. You can extend the formula down the column to extract the other surnames as well.
If you don’t know the exact position of your substring but instead know the general format of the string then you can still use the RIGHT function but combine it with either FIND or SEARCH functions and LEN function.
For this example, we will combine the RIGHT function with FIND and LEN.
=RIGHT(string, LEN(string) - FIND(text, string))
Where:
RIGHT function - tells Google Sheets to extract all rightmost characters from the string up to a certain position
String - where you want to extract substring
Text - the text that tells Google Sheets where to stop the extract process. If added to the formula, you should enclose it in double quotes
FIND function - tells Google Sheets where a specific text is located along a string
LEN function - tells Google Sheets to find the length of the string
Depending on the string, you may need to modify the formula. If a space is present before the substring you want to extract, add -1 after the FIND function:
=RIGHT(string, LEN(string) - FIND(text, string)-1)
This formula tells Google Sheets to do the following:
Example: We want to extract the first names from the following list of names:
The names are all located along Column A. For Row 2, the string is therefore stored in A2 and we will reference this in our formula. The text then is the comma symbol that separates the surname from the first name.
With the given values, we then set up the formula for Row 2 as
=RIGHT(A2, LEN(A2) - FIND(“,”, A2)-1)
We add the -1 because a space precedes the substring we want to extract.
We then input it to cell B2.
Alternatively, you can click the cell A2 while typing the function:
The cell B2 now contains the extracted first name. You can extend the formula down the column to extract the other surnames as well.
We hope this article has helped you and given you a better understanding of how to extract a Google Sheets substring. You might also like our articles on how to add bullet points in Google Sheets and how to find duplicates.
To optimize your workflow, we recommend reading our guide on how to send out mass emails.
