In this article:

How to Extract a Google Sheets Substring [2024 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:

google sheets extract substring from beginning, source string

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.

google sheets extract substring from beginning, set up left formula

Besides typing the whole formula, another way of setting it up is through the following steps:

  1. Typing =LEFT(
  2. Click cell A2
  3. Close the formula by typing , 6)
google sheets extract substring from beginning, set up left formula, screencap

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.

google sheets extract substring from beginning output

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:

Extract substring from the middle in google sheets, source string

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.

Extract substring from the middle in google sheets, set up mid formula

Alternatively, you can do the following steps:

  1. Type =MID(
  2. Click cell A2
  3. Close the formula by typing ,4, 3)
Extract substring from the middle in google sheets, set up mid formula screencap

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 middle in google sheets output

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:

google sheets get substring from the end, source string

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.

google sheets get substring from the end, set up right formula

Besides typing the whole formula, another way of setting it up is through the following steps:

  1. Typing =RIGHT(
  2. Click cell A2
  3. Close the formula by typing , 3)
google sheets get substring from the end, set up right formula screencap

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.

google sheets get substring from the end output

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:

  1. Find the position of a text along a string
  2. 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:

google sheet substring before certain text, source string

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.

google sheet substring before certain text, set up left plus find formula

Alternatively, you can do the following steps:

  1. Type =LEFT(
  2. Click cell A2
  3. Type FIND(“,”, 
  1. Click cell A2
  2. Close the formula by typing )-1)
google sheet substring before certain text, set up left plus find formula, screencap


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.

google sheet substring before certain text output

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:

  1. Calculate the length of the string
  2. Find the position of a given text along that string
  3. 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:

google sheets substring function after certain text, source string

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.

google sheets substring function after certain text, set up right plus find formula

Alternatively, you can click the cell A2 while typing the function:

google sheets substring function after certain text, set up right plus find formula, screencap

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.

google sheets substring function after certain text output

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 and trying our software for tracking loan portfolio deadlines.

Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started