In this article:

May 8, 2024

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:

- Typing
**=LEFT(** - Click cell A2
- Close the formula by typing
**, 6)**

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:

- Type
**=MID(** - Click cell A2
- Close the formula by typing
**,4, 3)**

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:

- Typing
**=RIGHT(** - Click cell A2
- Close the formula by typing
**, 3)**

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:

- Find the position of a
**text**along a**string** - Extract all characters before the given position

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:

- Type
**=LEFT(** - Click cell A2
- Type
**FIND(“,”,**

- Click cell A2
- Close the formula by typing
**)-1)**

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:

- Calculate the length of the
**string** - Find the position of a given
**text**along that**string** - Extract all characters before the given position

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->