TextSplit vs Text to Columns in Excel

Hello everyone! Today I show you how to split text in Excel using TEXTSPLIT formula and Text to Columns feature step by step with simple examples.

You ever have full name like "John Sophea Dara" in one cell and want to split into 3 columns (First Name, Middle Name, Last Name)? Or have email address like "info@rean.me" and want to split username and domain? Excel have two great ways to do this: the built-in Text to Columns wizard tool, and the dynamic TEXTSPLIT formula!

Let's look at how to use both method below.

1. How to Use TEXTSPLIT Formula in Excel (Excel 365 & Web)

TEXTSPLIT is a dynamic array formula in Excel 365. It automatically splits text across columns or rows using a delimiter (like space " ", comma ,, or dash -). Because it is a formula, if your original text changes, the split result updates automatically!

Formula Syntax:

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

Example:

If cell A2 contains "John Sophea Dara":

=TEXTSPLIT(A2, " ")

• Result: Spills across 3 cells ➔ [John] | [Sophea] | [Dara]

If cell A2 contains "apple,banana,orange":

=TEXTSPLIT(A2, ",")

• Result: [apple] | [banana] | [orange]

2. How to Use Text to Columns in Excel (All Excel Versions)

If you use older Excel version (like Excel 2016, 2013, or 2010), TEXTSPLIT formula is not available. But don't worry! You can use the built-in Text to Columns tool in Excel Data tab.

How to access:

Data Tab ➔ Text to Columns ➔ Delimited ➔ Choose Delimited character (Space/Comma/Tab) ➔ Finish

Step by Step:

1. Select the column containing text you want to split.
2. Go to Data tab on ribbon menu.
3. Click Text to Columns.
4. Choose Delimited ➔ Click Next.
5. Check your delimiter box (e.g. Space or Comma).
6. Select Destination cell ➔ Click Finish.

💡 Quick Tip:

• Use TEXTSPLIT formula if you are on Excel 365 or Excel for Web because it is dynamic and updates automatically when source data changes.
• Use Text to Columns if you are on older Excel versions, or when you want a one-time quick static split without writing any formulas!

🛠️ Free Online Splitter Tools

Want to split text online with 1 click? Check out these free tools:

• 🛠️ Free Advance Tool to Split Text with 1 Click
• 📅 Free Date & Time Splitter Online Tool

📚 Useful Resources

• How to Use Text Cleaning Formulas in Excel (LEFT, RIGHT, MID, LEN, TRIM, CLEAN)
• Concat vs TextJoin Function in Excel (Video in Khmer)
• Find vs Search Function in Excel (Video in Khmer)
• Top 15 Basic Excel Formulas Every Beginner Should Know
• Free Download PDF - 50 Popular Excel Formulas Cheatsheet (Khmer Language)