TEXTBEFORE and TEXTAFTER

In Lesson 11 we used LEFT, MID, and FIND together to extract text around a delimiter — and it worked, but the formulas were long and fragile. TEXTBEFORE and TEXTAFTER are the clean modern replacements. They extract everything before or after a specific delimiter with a single readable formula, handle multiple instances, support arrays of delimiters, and make text extraction far simpler for the messy structured strings you encounter in real data work.

TEXTBEFORE — Extract Everything Before a Delimiter

TEXTBEFORE returns all the text that appears before a specified delimiter. Think of it as "give me the left side of this separator" — but smarter, because you can choose which occurrence of the separator to use.

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

  text          →  The text string or cell reference
  delimiter     →  The character or string that marks the split point
  instance_num  →  Which occurrence to split at (1 = first, -1 = last)
  match_mode    →  0 = case-sensitive (default),  1 = case-insensitive
  match_end     →  1 = return full string if delimiter not found (instead of error)
  if_not_found  →  Custom text to show if the delimiter is not present

TEXTAFTER — Extract Everything After a Delimiter

TEXTAFTER is the mirror of TEXTBEFORE. Same arguments, opposite direction. Where TEXTBEFORE returns the left side, TEXTAFTER returns the right side. The two functions are designed to work as a complementary pair.

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

Cell A2: "james.smith@company.com"

=TEXTAFTER(A2, "@")          →  "company.com"
=TEXTAFTER(A2, ".")          →  "smith@company.com"   (after FIRST dot)
=TEXTAFTER(A2, ".", 2)       →  "company.com"         (after SECOND dot)
=TEXTAFTER(A2, ".", -1)      →  "com"                 (after LAST dot)

Instance Numbers — Counting from Either End

The third argument — instance_num — controls which occurrence of the delimiter to use as the split point. Positive numbers count from the left, negative numbers count from the right. This single argument replaces complicated chains of nested FIND formulas.

Practical Example — Email Address Parsing

Email addresses follow a consistent structure and appear in almost every business dataset. Here is how to extract every component cleanly — username, domain, and extension — with no helper columns.

The domain formula nests the two functions — TEXTAFTER extracts everything after the @ sign, then TEXTBEFORE pulls everything before the first dot in that result. Chaining the two functions this way replaces multi-level FIND/MID nesting with something that reads almost like plain English.

Practical Example — File Paths and Product Codes

File paths, reference codes, and URL strings all follow predictable delimiter patterns. Because negative instance numbers count from the right, extracting the last segment is just as clean as extracting the first.

File path in A2: "C:\Reports\Finance\Q1-2026\summary.xlsx"

Filename only (last segment after final backslash):
=TEXTAFTER(A2, "\", -1)                         →  "summary.xlsx"

Filename without extension:
=TEXTBEFORE(TEXTAFTER(A2, "\", -1), ".")         →  "summary"

Folder path without filename:
=TEXTBEFORE(A2, "\", -1)                         →  "C:\Reports\Finance\Q1-2026"

Product code in B2: "GB-NORTH-2024-W04"

Country prefix (before first hyphen):
=TEXTBEFORE(B2, "-")                             →  "GB"

Year (between second and third hyphen):
=TEXTBEFORE(TEXTAFTER(B2, "-", 2), "-")          →  "2024"

Item code (after last hyphen):
=TEXTAFTER(B2, "-", -1)                          →  "W04"

Multiple Delimiters at Once

Both functions accept an array of delimiter options inside curly braces. Excel matches the first occurrence of any of the supplied characters. This is useful when the delimiter is inconsistent across rows — for example, some records use a slash and others a hyphen.

Cell A2: "2024/03/15"    Cell A3: "2024-03-15"

Extract year regardless of separator:
=TEXTBEFORE(A2, {"/","-"})        →  "2024"
=TEXTBEFORE(A3, {"/","-"})        →  "2024"

Extract day (last segment):
=TEXTAFTER(A2, {"/","-"}, -1)     →  "15"
=TEXTAFTER(A3, {"/","-"}, -1)     →  "15"

This cleanly solves the mixed-separator problem that otherwise requires IF + ISNUMBER(FIND()) logic for each possible delimiter. An array of delimiters handles all options in one formula — and the first match wins, left to right.

Handling Missing Delimiters

If the delimiter you specify is not present in the text, both functions return a #N/A error by default. You have two ways to handle this gracefully — the if_not_found argument or match_end.

Cell A2: "NoAtSignHere"

Default — delimiter missing:
=TEXTBEFORE(A2, "@")                       →  #N/A

Using if_not_found (6th argument):
=TEXTBEFORE(A2, "@", 1, 0, 0, "No email") →  "No email"

Using match_end = 1 (5th argument):
=TEXTBEFORE(A2, "@", 1, 0, 1)             →  "NoAtSignHere"
(returns the full original string when the delimiter is absent)

The match_end = 1 option is useful when building formulas that need to work even when a delimiter is optional. The if_not_found option is better when you want to flag the missing value with a meaningful message rather than silently returning the whole string.

Old Way vs New Way

The classic approach to extracting text around a delimiter required chaining LEFT, MID, LEN, and FIND. The new approach is dramatically shorter and easier to read.

Next up — FILTER (Advanced), where we take a deeper look at multi-condition filtering, nested FILTER formulas, and building complete data retrieval pipelines by combining FILTER with SORT, UNIQUE, and XLOOKUP.