How to Use the TEXTBEFORE and TEXTAFTER Functions in Google Sheets

Data cleaning, text parsing, and string extraction are foundational tasks in spreadsheet management. Historically, extracting words or codes appearing before or after specific delimiters in Google Sheets required cumbersome combinations of LEFT, RIGHT, MID, FIND, SEARCH, and LEN functions, or complex regular expressions using REGEXEXTRACT.

Google Sheets simplified this workflow by introducing two dedicated modern text functions: TEXTBEFORE and TEXTAFTER. These functions extract text segments preceding or following specified delimiter characters with built-in support for case sensitivity, occurrence matching, and custom error fallbacks.

Syntax and Core Arguments

Both functions share an identical argument structure:

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

• text: The source text or cell reference containing the string you want to extract from.
• delimiter: The character or substring that marks the extraction boundary.
• instance_num (Optional): Specifies which occurrence of the delimiter to evaluate (default is 1 for the first occurrence; negative values count backwards from the end of the string).
• match_mode (Optional): 0 for case-sensitive matching (default); 1 for case-insensitive matching.
• match_end (Optional): 0 treats the start/end of the text as a non-match; 1 treats the end of the text as a valid delimiter.
• if_not_found (Optional): A custom fallback value (such as "" or "N/A") returned if the delimiter is not found, avoiding #N/A formula errors.

Extracting Usernames and Domains from Email Addresses

One of the most common applications of these functions is parsing contact lists:

• Extracting Username (Text Before @):

  =TEXTBEFORE(A2, "@")

  If cell A2 contains [email protected], this formula returns sarah.connor.
• Extracting Domain Name (Text After @):

  =TEXTAFTER(A2, "@")

  This formula extracts example.com directly from cell A2.

Handling Multiple Delimiters and Negative Instances

In product codes, inventory SKUs, or filesystem paths containing multiple repeating delimiters (such as hyphens or slashes), the instance_num parameter provides precise extraction control:

Suppose cell A2 contains the SKU string UK-LON-DEPT-9842-ARCH:

• Extracting the first segment:

  =TEXTBEFORE(A2, "-")

  Returns: UK.
• Extracting everything before the second hyphen:

  =TEXTBEFORE(A2, "-", 2)

  Returns: UK-LON.
• Extracting the final suffix using negative indexing:

  =TEXTAFTER(A2, "-", -1)

  Passing -1 instructs Google Sheets to search backwards from the end of the string, returning the final segment: ARCH.
• Extracting everything before the final suffix:

  =TEXTBEFORE(A2, "-", -1)

  Returns: UK-LON-DEPT-9842.

Combining TEXTBEFORE and TEXTAFTER into Array Formulas

To process thousands of rows simultaneously without dragging formulas down columns, wrap the functions inside ARRAYFORMULA:

=ARRAYFORMULA(IF(A2:A="", "", TEXTBEFORE(A2:A, "@")))

This dynamic array formula automatically scans column A and populates corresponding usernames across the entire dataset dynamically as new records are added.