How to Use TEXTBEFORE and TEXTAFTER Functions in Microsoft Excel

Cleaning and parsing textual data in Microsoft Excel historically required composing brittle, multi-layered formula nests. Splitting a string at a comma, hyphen, or space meant combining LEFT, RIGHT, or MID with FIND, SEARCH, and LEN. A single missing character or irregular space would yield frustrating #VALUE! calculation errors. Microsoft eliminated this formula complexity with the introduction of the dynamic array text functions: TEXTBEFORE and TEXTAFTER. These functions allow spreadsheet users to extract precise text substrings based on delimiter boundaries in a single, intuitive formula.

Understanding the Syntax of TEXTBEFORE and TEXTAFTER

Both functions share an identical structural logic, differing only in whether they extract text preceding or succeeding the specified delimiter string.

The standard syntax for TEXTBEFORE:

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

The standard syntax for TEXTAFTER:

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

Let’s examine what each argument controls:

• text: The source cell or string you want to evaluate.
• delimiter: The character or substring marking the boundary (e.g., " ", "-", or "@").
• instance_num (optional): Which occurrence of the delimiter to split at. Defaults to 1 (first occurrence). A negative number counts backwards from the end of the string (e.g., -1 splits at the last occurrence).
• match_mode (optional): Determines case sensitivity. 0 enforces case sensitivity (default); 1 is case-insensitive.
• match_end (optional): Treats the beginning or end of the text as a delimiter. 0 disables this (default); 1 matches the boundary.
• if_not_found (optional): A fallback string returned if the delimiter is absent, preventing #N/A errors.

Practical Examples: Extracting Names, Emails, and Codes

Review these common text-cleaning scenarios and their straightforward formula implementations:

1. Extracting First Names and Surnames

If cell A2 contains "Alexander Graham":

• To extract the first name: =TEXTBEFORE(A2, " ") returns "Alexander".
• To extract the surname: =TEXTAFTER(A2, " ") returns "Graham".

2. Extracting Email Usernames and Domains

If cell A2 contains "[email protected]":

• To pull the username before the “@” symbol: =TEXTBEFORE(A2, "@") returns "sarah.connor".
• To pull the domain name: =TEXTAFTER(A2, "@") returns "example.com".

3. Parsing Product Codes with Multiple Hyphens

If cell A2 contains "LON-DEPT4-SKU8842-FINAL":

To extract only the serial code (SKU8842), combine both functions:

=TEXTBEFORE(TEXTAFTER(A2, "-", 2), "-")

Here, TEXTAFTER(A2, "-", 2) isolates everything after the second hyphen ("SKU8842-FINAL"), and TEXTBEFORE(..., "-") extracts the text before the next hyphen, cleanly returning "SKU8842".

Formula Arguments and Behaviour Comparison

Argument	Required	Default Value	Key Capability
text	Yes	N/A	Source string or cell reference
delimiter	Yes	N/A	Boundary string; can accept an array of delimiters {"-", "/"}
instance_num	No	1	Selects specific delimiter index; negative numbers count from right
match_mode	No	0 (Exact)	Set to 1 to ignore upper/lower case distinctions
if_not_found	No	#N/A	Custom fallback return value if delimiter is missing

Handling Delimiter Arrays and Missing Matches Safely

In real-world spreadsheets, data entry often contains inconsistent separators—some records use slashes, while others use hyphens or commas. You can pass an array constant of delimiters directly into TEXTBEFORE or TEXTAFTER:

=TEXTBEFORE(A2, {"-", "/", "|"}, 1, 0, 0, "No Delimiter Found")

Excel checks for any of the three delimiter symbols. If none exist in the target string, rather than yielding an error badge, the formula gracefully outputs "No Delimiter Found".

Frequently Asked Questions

Which versions of Microsoft Excel support TEXTBEFORE and TEXTAFTER?

These functions are supported in Microsoft 365 (Windows, Mac, and Web) and Excel 2024. Legacy standalone releases such as Excel 2019 and Excel 2016 do not support these functions; they rely on classic LEFT/FIND formulas.

Can TEXTBEFORE and TEXTAFTER spill down columns dynamically?

Yes. When passed a range reference such as =TEXTBEFORE(A2:A100, " "), Excel leverages its dynamic array calculation engine to spill results across all 99 rows automatically without dragging fill handles.

How do I extract the text after the final space in a multi-word string?

Use a negative instance number: =TEXTAFTER(A2, " ", -1). The -1 instructs Excel to start searching from the end of the string backwards, isolating the final token.