Excel 365 has introduced some powerful text functions that save a lot of time in cleaning, formatting, and restructuring data. Two of the most useful functions are TEXTSPLIT and TEXTJOIN in Excel.
If you’ve ever struggled with separating data into multiple cells or combining multiple cells into one neatly formatted text, these functions are for you. Let’s understand them step by step with real-life examples – from basic to advanced.
1. What is TEXTSPLIT in Excel?
The TEXTSPLIT() function helps you split text into multiple cells using a delimiter (such as space, comma, dash, etc.).
Syntax:
=TEXTSPLIT(text, col_delimiter, [row_delimiter])
- text → The cell or text you want to split.
- col_delimiter → The character that separates columns (e.g., space, comma).
- row_delimiter (optional) → The character that separates rows (e.g., line break).
Example 1: Split Names into First and Last Name
| A (Full Name) | Formula | Result |
|---|---|---|
| John Doe | =TEXTSPLIT(A2, " ") | John (in B2), Doe (in C2) |
👉 This splits a full name into First Name and Last Name.
Example 2: Split Product Codes into Different Columns
Suppose you have product codes like PRD-2025-IND.
Formula:
=TEXTSPLIT(A2, "-")
Result:
| A (Code) | B | C | D |
|---|---|---|---|
| PRD-2025-IND | PRD | 2025 | IND |
👉 Useful when working with SKU codes or invoice numbers.
Example 3: Split Data into Rows
If your cell contains:Apple; Mango; Banana; Orange
Formula:
=TEXTSPLIT(A2, , ";")
Arguments Breakdown:
- A2 → This is the cell that contains the text you want to split.
- , (col_delimiter) → Notice it’s left blank here. That means you’re not splitting into columns.
- “;” (row_delimiter) → This tells Excel to split the text into rows wherever it finds a semicolon (
;).
Result:
- Apple
- Mango
- Banana
- Orange
👉 Helps when you want to list items vertically.
Read More: TEXTBEFORE & TEXTAFTER in Excel
2. What is TEXTJOIN in Excel?
The TEXTJOIN() function allows you to combine multiple text values into one cell, separated by a chosen delimiter.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter → What separates the combined values (e.g., comma, space, dash).
- ignore_empty → TRUE/FALSE (whether to ignore blank cells).
- text1, text2 → The cells or text to join.
Example 4: Join First and Last Name
| B (First) | C (Last) | Formula | Result |
|---|---|---|---|
| John | Doe | =TEXTJOIN(" ", TRUE, B2, C2) | John Doe |
👉 Similar to using & operator, but much more flexible.
Example 5: Combine Email IDs into One Cell
Suppose you have email IDs in B2:B5.
Formula:
=TEXTJOIN(", ", TRUE, B2:B5)
Result:a@gmail.com, b@yahoo.com, c@outlook.com
👉 Perfect for copy-pasting a list of emails into Outlook.
Example 6: Create Address from Multiple Cells
| A (House) | B (Street) | C (City) | Formula | Result |
|---|---|---|---|---|
| 101 | MG Road | Mumbai | =TEXTJOIN(", ", TRUE, A2:C2) | 101, MG Road, Mumbai |
👉 Helps in cleaning customer addresses.
3. Advanced Use Cases (TEXTSPLIT + TEXTJOIN Together)
Example 7: Split and Rejoin Cleaned Data
Suppose you have:Apple,,Mango,,,Banana
Formula:
=TEXTJOIN(", ", TRUE, TEXTSPLIT(A2, ","))
Result:Apple, Mango, Banana
👉 Splits the text first, removes empty values, and then rejoins cleanly.
Example 8: Extract First Names from Email IDs
Emails like:john.doe@gmail.com
Formula:
=TEXTBEFORE(TEXTSPLIT(A2, "@"), ".")
Result:john
👉 A mix of TEXTSPLIT with TEXTBEFORE for real data cleaning.
4. When to Use TEXTSPLIT and TEXTJOIN?
- ✅ Use TEXTSPLIT when you need to separate values into columns/rows.
- ✅ Use TEXTJOIN when you want to combine multiple cells into a single neat string.
- ✅ Combine both for advanced data cleaning and reporting.
5. Real-Life Scenarios
- HR teams: Splitting employee names and combining addresses.
- Finance: Breaking invoice codes and creating summary reports.
- MIS Reporting: Cleaning messy text data before analysis.
- Marketing: Joining multiple campaign IDs into one report.
Final Thoughts
The TEXTSPLIT and TEXTJOIN functions are game-changers for anyone working with large text datasets in Excel. They help you save time, avoid manual copy-pasting, and make data clean and professional.
Once you master these, you’ll never struggle with splitting or merging data again.
What’s Next?
In the next post, we’ll learn about the 20 Other Useful Excel 365 Functions