How to Use the TEXTBEFORE and TEXTAFTER Functions in Google Sheets to Extract Substrings

String manipulation is a cornerstone of spreadsheet data cleaning. When preparing raw datasets in Google Sheets—such as parsing email addresses, separating customer names, extracting SKU product codes, or isolating domain names from URLs—analysts historically relied on nesting convoluted combinations of LEFT, RIGHT, MID, FIND, and LEN formulas. These legacy string extraction formulas were notorious for being difficult to read, error-prone when delimiters were absent, and fragile across edge cases. Google Sheets modernised string parsing with the introduction of the native TEXTBEFORE and TEXTAFTER functions, providing clean, declarative formulas for extracting substrings based on delimiters.

Understanding the Syntax of TEXTBEFORE and TEXTAFTER

Both functions share nearly identical, intuitive parameter architectures:

TEXTBEFORE Syntax

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

TEXTAFTER Syntax

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

The components of the formulas operate as follows:

• text: The source cell or text string you want to extract from (e.g. A2).
• delimiter: The boundary character or string that marks where to cut (e.g. "@", "-", or "/").
• [instance_num] (Optional): Specifies which occurrence of the delimiter to use. Defaults to 1 (first occurrence). Supplying negative numbers (e.g. -1) counts backward from the end of the string.
• [match_mode] (Optional): Set to 0 for case-sensitive matching (default), or 1 for case-insensitive matching.
• [match_end] (Optional): Controls whether the start or end of text is treated as a delimiter.
• [if_not_found] (Optional): Custom fallback value returned if the delimiter is missing, avoiding ugly #N/A errors.

Basic Examples: Parsing Email Addresses

Suppose cell A2 contains the email address [email protected].

1. Extracting the Username (Before @)

=TEXTBEFORE(A2, "@")

Returns: john.smith

2. Extracting the Domain Name (After @)

=TEXTAFTER(A2, "@")

Returns: digitash.com

Advanced Delimiter Handling: Instance Numbers and Negative Indexing

Real-world data often contains strings with repeated delimiters, such as file paths or product SKU codes (e.g. CORP-SALES-2026-FINAL in cell A2).

Extracting After the Second Hyphen

To extract everything after the second occurrence of the hyphen (-):

=TEXTAFTER(A2, "-", 2)

Returns: 2026-FINAL

Extracting the Last Segment (Negative Indexing)

Instead of counting how many slashes exist in a URL or directory path, use -1 to target the last delimiter from the right:

Suppose cell A2 contains https://digitash.com/linux/terminal/commands/:

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

If trailing slashes exist, extract the slug by isolating between delimiters:

=TEXTBEFORE(TEXTAFTER(A2, "terminal/"), "/")

Handling Missing Delimiters with Fallback Values

If a source cell lacks the requested delimiter, standard extraction formulas crash with an unsightly #N/A error. With TEXTBEFORE and TEXTAFTER, you can define an inline fallback using the sixth parameter without wrapping the formula inside IFNA() or IFERROR():

=TEXTBEFORE(A2, "@", 1, 0, 0, "No Email Provided")

If cell A2 contains plain text without an @ symbol, the formula cleanly displays No Email Provided.

Using ArrayFormula to Process Entire Columns

You can process thousands of records simultaneously by wrapping your formula inside MAP and LAMBDA:

=MAP(A2:A100, LAMBDA(email, IF(email="", "", TEXTBEFORE(email, "@"))))

This dynamic array spills usernames down column B automatically without manual formula dragging.

Key Benefits Over Legacy Spreadsheet Formulas

Task	Legacy Approach	Modern Approach
Extract before character	=LEFT(A2, FIND("@", A2) - 1)	=TEXTBEFORE(A2, "@")
Extract after character	=RIGHT(A2, LEN(A2) - FIND("@", A2))	=TEXTAFTER(A2, "@")
Extract after last delimiter	Complex multi-nested SUBSTITUTE and FIND formulas	=TEXTAFTER(A2, "/", -1)

Adopting TEXTBEFORE and TEXTAFTER dramatically improves spreadsheet readability, reduces calculation overhead, and prevents brittle formula breakage when manipulating text strings in Google Sheets.