TEXTBEFORE in Google Sheets (Easiest Way to Use It in 2024)

TEXTBEFORE in Google Sheets

While Google Sheets is a powerful tool for data analysis, it lacks the TEXTBEFORE function which is a useful utility found in Microsoft Excel for extracting text from a cell that appears before a specified delimiter. 

Challenge

In scenarios where you need to extract a portion of text from a string before a specific delimiter, for example, retrieving a first name from a full name where the names are separated by a space, Google Sheets doesn't offer a direct TEXTBEFORE function like Excel does.

Solution 

To emulate TEXTBEFORE functionality in Google Sheets, you can use the REGEXEXTRACT function. This function allows for pattern matching and extraction based on regular expressions, offering a flexible way to parse strings.

‘REGEXEXTRACT’ Syntax

Given a string in cell A1, such as "John Doe - CEO", and the goal to extract "John Doe", the following formula demonstrates how to use REGEXEXTRACT for this purpose:

‍

=REGEXEXTRACT(A1, "^(.*?)( -|$)")

‍

This formula works as follows:

‍

• The ^ asserts the start of the string.
• (.*?) is a non-greedy match for any characters in the string, capturing as few characters as possible until it encounters the following pattern.
• ( -|$) matches the delimiter " -" or the end of the string ($), ensuring compatibility whether or not the delimiter is present.

How to Use REGEXEXTRACT as TEXTBEFORE

Follow the steps below to use REGEXEXTRACT as TEXTBEFORE in Google Sheets. 

1. Locate the Cell With Text to Extract

Find the cell containing the text you wish to extract from. Suppose "John Doe - CEO" is in cell A1, and you want to extract "John Doe".

2. Select the Destination Cell for Extracted Text

Click on an empty cell where the result will appear. This is where the extracted text will be displayed. If you're working with the text in A1, you might choose B1 for the formula result.

3. Begin the REGEXEXTRACT Formula in the Destination Cell

In the selected cell, begin typing the REGEXEXTRACT formula by entering =REGEXEXTRACT(.

4. Input the Reference of the Source Cell Into the Formula

Continue the formula by adding the reference to the source cell, then a comma. For our example, you would type A1,. This tells Google Sheets your text is in cell A1.

5. Define the Regular Expression to Specify Text Before the Delimiter

Now, add the regular expression inside double quotes. To extract text before " -", use ^(.*?)( -|$). Complete the formula: =REGEXEXTRACT(A1, "^(.*?)( -|$)").

6. Execute the Formula to View the Extracted Text

Press Enter to apply the formula. The cell where you typed the formula will now display the extracted text, "John Doe", from A1.

7. Modify the Formula for Different Text or Delimiters as Needed

If your text or delimiter differs, modify the cell reference or the regular expression in the formula accordingly.

‍

We hope that you now have a better understanding of how to use REGEXEXTRACT as TEXTBEFORE in Google Sheets. If you enjoyed this article, you might also like our article on how to set up footnotes in Google Sheets or our article on how to conditionally delete rows in Google Sheets.