TEXTBEFORE & TEXTAFTER in Excel

Have you ever needed to split a string in Excel based on a specific word, symbol, or character?

Meet two powerful new functions in Excel 365:
👉 TEXTBEFORE
👉 TEXTAFTER

These functions make it super easy to extract data before or after a specific delimiter like a comma, dash, space, or even a word!

Let’s dive into real-life examples to learn how to use them smartly in your daily MIS or data cleaning tasks.

Table of Contents

What are TEXTBEFORE and TEXTAFTER?

TEXTBEFORE

This function returns the text that appears before a specific character or word.

Syntax:

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

TEXTAFTER

This function returns the text that appears after a specific character or word.

Syntax:

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

Basic Examples

Example 1: Split Email into Username and Domain

A (Email)	B (Username)	C (Domain)
john.doe@gmail.com	`=TEXTBEFORE(A2, "@")`	`=TEXTAFTER(A2, "@")`

Output:

B2: john.doe
C2: gmail.com

Also Read: XLOOKUP in Excel

Example 2: Get First and Last Name from Full Name

A (Full Name)	B (First Name)	C (Last Name)
Priya Sharma	`=TEXTBEFORE(A2, " ")`	`=TEXTAFTER(A2, " ")`

Output:

B2: Priya
C2: Sharma

Real-Life Practical Use Cases

Example 3: Extract Product Code and Description

A (Details)	B (Code)	C (Description)
P001 – Bluetooth Speaker	`=TEXTBEFORE(A2, " - ")`	`=TEXTAFTER(A2, " - ")`

Output:

Code: P001
Description: Bluetooth Speaker

Example 4: Extract File Name from File Path

A (File Path)	B (File Name)
C:\Users\Rahul\Documents\Report_July.xlsx	`=TEXTAFTER(A2, "\")`

⚠️ Wait! This only gets the text after first backslash.
So, let’s use a nested formula with TEXTAFTER:

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

👉 Here, -1 tells Excel to get the last occurrence of the backslash.

Output: Report_July.xlsx

Example 5: Nested TEXTBEFORE & TEXTAFTER — Extract Middle Value

A (Value)	B (Middle Value)
P001-Red-Large	`=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")`

What’s happening?

TEXTAFTER(A2,"-") → gives Red-Large
TEXTBEFORE(...,"-") → gives Red

Output: Red

Example 6: Extract Domain Name from URL

A (URL)	B (Domain)
https://www.smarttejas.com/blog	`=TEXTBEFORE(TEXTAFTER(A2,"//"),"/")`

Breakdown:

TEXTAFTER(A2,"//") → www.smarttejas.com/blog
TEXTBEFORE(...,"/") → www.smarttejas.com

Output: www.smarttejas.com

Also Read: FILTER Function in Excel

Example 7: Extract Numbers from Text

A (Text)	B (Extracted Number)
Order#12345 Completed	`=TEXTAFTER(A2, "#")`

Output: 12345 Completed

Want only number? Add a space:

=TEXTBEFORE(TEXTAFTER(A2, "#"), " ")

Output: 12345

Tips and Best Practices

Always check if your delimiter (like - or @) is consistent.
Use instance_num for multiple occurrences.
Use -1 for last occurrence — a hidden gem.
Combine with TEXTSPLIT for multiple values.
Avoid helper columns using nested formulas.

Common Errors & Fixes

Error	Reason	Fix
`#VALUE!`	Delimiter not found	Use the `if_not_found` argument
Wrong output	Multiple delimiters, but not specified	Use `instance_num`
Messy strings	Inconsistent formatting	Combine with `TRIM()` or `CLEAN()`

Summary

Function	What it Does	Common Use Case
`TEXTBEFORE`	Gets text before a delimiter	Extracting first name, code, prefix
`TEXTAFTER`	Gets text after a delimiter	Extracting domain, file name, suffix

Final Thoughts

TEXTBEFORE() and TEXTAFTER() are game-changing tools in Excel 365 — especially when working with names, codes, emails, URLs, product lists, or any structured string.

Forget complex combinations of LEFT, RIGHT, LEN, and FIND…
These new functions simplify everything!

Spread the love