How to Use the TEXTSPLIT, TEXTBEFORE, and TEXTAFTER Functions in Microsoft Excel

Extracting substrings, parsing email addresses, and splitting delimited text strings in Microsoft Excel historically required complex combinations of legacy formulas like LEFT, RIGHT, MID, FIND, and SEARCH, or static utilities like Text to Columns. With the introduction of modern dynamic text functions—TEXTSPLIT, TEXTBEFORE, and TEXTAFTER—Excel users can parse multi-delimiter strings, isolate prefixes and domains, and spill segmented data across adjacent columns or rows dynamically.

Core Syntax and Operational Mechanics

These three formulas eliminate the need for nested length calculations and helper columns:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Function	Output Behavior	Primary Use Case
TEXTSPLIT	Dynamic array spill (columns or rows)	Splitting full names, comma-separated lists, and multi-line cells.
TEXTBEFORE	Single text value	Extracting usernames, area codes, or product category prefixes.
TEXTAFTER	Single text value	Extracting email domains, file extensions, or trailing transaction IDs.

Example 1: Parsing Full Names and Multi-Delimiter Lists with TEXTSPLIT

Suppose cell A2 contains: "Alice; Bob, Charlie; Diana" where delimiters are inconsistent (both semicolons and commas).

You can pass an array constant of delimiters directly into TEXTSPLIT:

=TEXTSPLIT(A2, {",", ";"}, , TRUE)

How it executes:

1. Excel searches for both commas and semicolons.
2. Setting ignore_empty = TRUE prevents empty cells when both delimiters appear together.
3. The formula dynamically spills: Alice | Bob | Charlie | Diana horizontally across adjacent columns.
4. To spill vertically into rows instead of columns, leave col_delimiter empty and pass the array into row_delimiter:

   =TEXTSPLIT(A2, , {",", ";"}, TRUE)

Example 2: Extracting Usernames and Domains with TEXTBEFORE and TEXTAFTER

Given an email address in cell B2: "[email protected]".

Extract the username before the “@” symbol:

=TEXTBEFORE(B2, "@")
# Returns: "sarah.connor"

Extract the domain after the “@” symbol:

=TEXTAFTER(B2, "@")
# Returns: "cyberdyne.corp"

Example 3: Handling Multiple Occurrences with instance_num

When text contains repeated delimiters (such as file paths or URLs), the instance_num parameter specifies which delimiter to target. Using a negative instance number counts from right to left.

Suppose cell C2 contains: "C:EngineeringProjectsFinal_Report_v2.xlsx".

To extract the file extension (everything after the final period):

=TEXTAFTER(C2, ".", -1)
# Returns: "xlsx"

To extract the filename itself (everything after the final backslash):

=TEXTAFTER(C2, "", -1)
# Returns: "Final_Report_v2.xlsx"

To extract the folder path (everything before the final backslash):

=TEXTBEFORE(C2, "", -1)
# Returns: "C:EngineeringProjects"

By replacing brittle character-counting formulas with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER, spreadsheet models become clearer, faster, and immune to variable-length text errors.