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

📘 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"

---

✅ Bonus: Why Use These?

• Avoids complex combinations of LEFT, RIGHT, FIND, and LEN
• Works dynamically on arrays and ranges
• Simplifies text cleaning and parsing in data analysis

---