Table of Contents
To extract text before a specific character in Google Sheets, you can use the “LEFT” function. This function allows you to specify the number of characters you want to extract from the left side of a cell, before a specific character. By using this function, you can easily retrieve the desired text from a cell without manually copying and pasting. This method is particularly useful for separating data and organizing it into different columns.
Google Sheets: Extract Text Before a Character
You can use the following formula in Google Sheets to extract all text before a specific character in a cell:
=REGEXEXTRACT(A2,"(.*)our.*")
This particular formula extracts all of the text in cell A2 that occurs before the string “our” is encountered.
This formula uses the REGEXTRACT function to extract all characters (.*) before our.
The following example shows how to use this formula in practice.
Example: Extract Text Before Character in Google Sheets
Suppose we have the following list of phrases in Google Sheets:
Now suppose that we would like to extract all text from each cell before the string “our” is encountered.
To do so, we can type the following formula into cell B2:
=REGEXEXTRACT(A2,"(.*)our.*")
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays all text before “our” for each phrase in column A.
To extract the text before a different specific character, we simply need to replace our with something else.
For example, we could type the following formula into cell B2 to extract all text before “is ” from cell A2:
=REGEXEXTRACT(A2,"(.*)is .*")
Column B now displays all text before “is ” for each phrase in column A.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: