How to Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel 365 with Real-World Examples

Jun 26, 2025

—

by

📘 Overview of the Functions

These text functions are new in Excel 365 and Excel 2021, part of the dynamic array functions family. They are useful for splitting or extracting parts of text based on delimiters (like commas, spaces, hyphens, etc.).

🔹 1. TEXTBEFORE

✅ Purpose:

Extracts the text before a specified delimiter.

📘 Syntax:

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

🔧 Scenario:

You have email addresses in a list and want to extract usernames (text before @).

🧪 Example:

excelCopyEdit=TEXTBEFORE("john.doe@gmail.com", "@")

➡️ Result: john.doe

🔹 2. TEXTAFTER

✅ Purpose:

Extracts the text after a specified delimiter.

📘 Syntax:

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

🔧 Scenario:

From an email, you want to extract just the domain name.

🧪 Example:

excelCopyEdit=TEXTAFTER("john.doe@gmail.com", "@")

➡️ Result: gmail.com

🔹 3. TEXTSPLIT

✅ Purpose:

Splits a text string into rows or columns using one or more delimiters.

📘 Syntax:

excelCopyEdit=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

🔧 Scenario:

You have full names like "John,Doe" and want to split them into first name and last name in two columns.

🧪 Example:

excelCopyEdit=TEXTSPLIT("John,Doe", ",")

➡️ Result:

A	B
John	Doe

🔄 Combined Real-World Example

🎯 Scenario:

You have a product list like this:

arduinoCopyEdit"SKU123-Apple-Fruit"
"SKU456-Banana-Fruit"

And you want to extract:

A	B	C	D
SKU456-Banana-Fruit	SKU456	Banana	Fruit

🧪 Formulas:

To get the SKU:

excelCopyEdit=TEXTBEFORE(A1, "-")

To get the Fruit Name:

excelCopyEdit=TEXTSPLIT(TEXTAFTER(TEXTBEFORE(A1,"-Fruit"), "-"), "-")

To get the Category:

excelCopyEdit=TEXTAFTER(A1, "-", 2)

📝 Summary Table

Function	Use Case Example	Description
`TEXTBEFORE`	`TEXTBEFORE("file.docx", ".")`	Returns `"file"` before `.`
`TEXTAFTER`	`TEXTAFTER("file.docx", ".")`	Returns `"docx"` after `.`
`TEXTSPLIT`	`TEXTSPLIT("John,Doe", ",")`	Splits into `"John"` and `"Doe"`