.png)
How to Extract a Google Sheets Substring [2023 Update]
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.
How to Extract a Google Spreadsheet Substring
Extract Substring from the Beginning of a String
Syntax
=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
1. Identify the cell containing the string and the length of substring
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.
2. Set up the formula =LEFT(string,length)
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:
- Typing =LEFT(
- Click cell A2
- Close the formula by typing , 6)
3. Press Enter
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.
Extract Substring from the Middle of a String
Syntax
=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
1. Identify the cell containing string, the starting location, and length of substring
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.
2. Set up the formula =MID(string,start_loc,length)
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:
- Type =MID(
- Click cell A2
- Close the formula by typing ,4, 3)
3. Press Enter
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.
Extract Substring from the End of a String
Syntax
=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
1. Identify the cell containing the string and the length of substring
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.
2. Set up the formula =RIGHT(string,length)
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:
- Typing =RIGHT(
- Click cell A2
- Close the formula by typing , 3)
3. Press Enter
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.
Get the Substring Before Certain Text
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.
Syntax
=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:
- Find the position of a text along a string
- Extract all characters before the given position
1. Identify the string and the text
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.
2. Use formula =LEFT(string, FIND(text, string)-1)
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:
- Type =LEFT(
- Click cell A2
- Type FIND(“,”,
- Click cell A2
- Close the formula by typing )-1)
3. Press Enter
The cell B2 now contains the extracted surname. You can extend the formula down the column to extract the other surnames as well.
Get Substring After Certain Text
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.
Syntax
=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:
- Calculate the length of the string
- Find the position of a given text along that string
- Extract all characters before the given position
1. Identify the string and the text
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.
2. Use formula =RIGHT(string, FIND(text, string))
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:
3. Press Enter
The cell B2 now contains the extracted first name. You can extend the formula down the column to extract the other surnames as well.
