Table of Contents
Google Sheets is a powerful tool that allows users to store, organize, and analyze data in a spreadsheet format. One useful feature of Google Sheets is the ability to extract text between two specific characters. This means that users can easily retrieve specific portions of text from a larger string of data. To do this, users can utilize the built-in formulas and functions in Google Sheets, such as the MID and FIND functions. These tools allow users to specify the starting and ending characters, and the function will return the text between them. This feature is particularly useful for tasks such as data cleaning, data manipulation, and data analysis. By being able to easily extract text between two characters, users can efficiently manage and work with their data in Google Sheets.
Google Sheets: Extract Text Between Two Characters
You can use the REGEXTRACT function in Google Sheets to extract all text between two specific characters in a cell.
You can use the following syntax to do so:
=REGEXEXTRACT(A2,"this(.*)that")
This particular formula extracts all of the text between the characters “this” and “that” in cell A2.
The following examples show several common ways to extract text between two characters in practice.
Example 1: Extract Text Between Strings
We can type the following formula into cell B2 to extract the text in cell A2 between the strings “ran” and “miles”:
=REGEXEXTRACT(A2,"ran(.*)miles")
We can then click and drag this formula down to each remaining cell in column B:
Column B contains the text between the strings “ran” and “miles” for each corresponding cell in column A.
Example 2: Extract Text Between Parentheses
We can type the following formula into cell B2 to extract the text in cell A2 between the parentheses:
=REGEXEXTRACT(A2,"((.*))")
We can then click and drag this formula down to each remaining cell in column B:
Column B contains the text between the parentheses for each corresponding cell in column A.
Example 3: Extract Text Between Asterisks
We can type the following formula into cell B2 to extract the text in cell A2 between the asterisks:
=REGEXEXTRACT(A2,"*(.*)*")
We can then click and drag this formula down to each remaining cell in column B:
Column B contains the text between the asterisks for each corresponding cell in column A.
Note: We had to use a slash as an escape character before the asterisks so that the REGEXTRACT function recognized these characters as asterisks.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Extract Text After a Character
Google Sheets: How to Extract Text Before a Character