6 Best Ways How to Extract Substring in Google Sheets

In this tutorial, I will teach you how to extract substring in Google Sheets using text functions.

Text functions in Google Sheets are functions specialized in processing cell values into text, text to different forms of texts, and text manipulation.

There are many text functions in Google Sheets, but I will focus on how to extract substring in Google Sheets using the functions LEFT, RIGHT, MID, SEARCH/FIND, and LEN

How to Extract Substring in Google Sheets

To Extract Substring in Google Sheets do the following:

• Extract by number of characters from the start of a string =LEFT([string],[number of characters])
• Extract by the number of characters before the end of a string =RIGHT([string],[number of characters])
• Extract all before a certain text =LEFT([string],SEARCH([text to search or find],[string])-1)
• Extract all after a certain text =RIGHT([string],LEN([string])-SEARCH([text to search or find],[string])-(LEN([text to search or find])-1))
• Extract by number of characters after an index =MID([string],[index],[number of characters])
• Extract by number of characters after a certain text =MID([string],SEARCH([text to search or find],[string])+LEN([text to search or find]),[number of characters])

For this tutorial, I will use generic text blocks to test my formulas on.

As such, I’ve provided a document containing short texts like an Aesop fable, and Lorem Ipsum through this link.

You can go ahead and duplicate it directly by clicking ‘Make a Copy’ under the ‘File’ tab.

With that out of the way, let’s explore the methods on How to Extract Substring in Google Sheets!

Method 1 – Extract by number of characters from the start of a string in Google Sheets

You can use the LEFT function to extract a substring according to a specified number of characters from the start of the string.

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] part.

Next, type in how many characters you want to extract from the start of the string on the [number of characters] part.

Let’s assume you want to extract 5 characters from the start of a text in cell A2, this is how the formula and the result should look like:

Method 2 – Extract by number of characters before the end of a string in Google Sheets

You can use the RIGHT function to extract a substring according to a specified number of characters before the end of the string.

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] part.

Next, type in how many characters you want to extract before the end of the string on the [number of characters] part.

To extract 10 characters before the end of a text in cell A2, this is how the formula and the result should look like:

Method 3 – Extract all before a certain text element in Google Sheets

You can use a combination of the LEFT function with either the SEARCH or FIND functions to extract all the characters before a specific text in a string.

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] parts.

Next, type in the text you want to search for on the [text to search or find] part.

To extract all characters before the word ‘fold’ from a text in cell A2, this is how the formula and result should look like:

Method 4 – Extract all after a certain text element in Google Sheets

You can use a combination of the RIGHT function with either the SEARCH or FIND functions to extract all the characters after a specific text in a string.

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] parts.

Next, type in the text you want to search for on the [text to search or find] parts.

To extract all characters after the word ‘refute’ from a text in cell A2, this is how the formula and result should look like:

Method 5 – Extract by the number of characters after an index in Google Sheets

You can use the MID function to extract a certain number of characters after an index.

Where an index is a position in the text defined by a number (1 as the first character in the text).

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] part.

Next, identify which part in the text you want to start extracting from by typing it in the [index] part.

Finally, type in how many characters you want to extract before the end of the string on the [number of characters] part.

Let’s assume that you want to extract 5 characters after the 17th character of the cell A2, this is how the formula and result should look like:

Method 6 – Extract by the number of characters after a certain text in Google Sheets

You can use a combination of the MID, LEN, and either the SEARCH or FIND functions to extract a certain number of characters after a certain text in Google Sheets.

From the formula shown above, simply reference the cell address of the string you want to extract from on the [string] parts.

Next, type in the text you want to search or find on the [text to search or find] parts.

Finally, type in how many characters you want to extract after the text to search and find on the [number of characters] part.

To extract 15 characters after the word ‘eat’ from a text in cell A2, this is how the formula and result should look like:

Frequently Asked Questions On How to Extract Substring in Google Sheets

Conclusion on How to Extract Substring in Google Sheets

You can:

• Extract certain number of characters as substring from start of string using the LEFT function
• Extract certain number of characters as substring before end of string using the RIGHT function
• Extract all characters before a certain text as substring using the LEFT and SEARCH functions
• Extract all characters after a certain text as substring using the RIGHT, LEN and SEARCH functions
• Extract certain number of characters after an index as substring using the MID function
• Extract certain number of characters after a certain text as substring using the MID, SEARCH and LEN functions