How to Split Data in Excel (2025) – 6 Easy Methods for Any User

How to Split Data in Excel (2025): Learn how to split one cell into multiple cells in Excel using 6 proven methods: Flash Fill, Text to Columns, TEXTSPLIT, Power Query & more. Works in Excel 365, 2021, and older versions.

If you’ve ever received data like John Doe, john@example.com or ProductA | 200 | Category1 and wanted to split it across multiple columns — you’re not alone.

In this post, we’ll cover 6 best ways to split data in Excel — from beginner-friendly options like Flash Fill and Text to Columns, to powerful modern functions like TEXTSPLIT, TEXTBEFORE, and Power Query.

Let’s dive into the most up-to-date ways to split one cell into multiple cells in Excel — no complex coding needed!

🧠 Why Split Cells in Excel?

When you import data from CSV, databases, or forms, it often appears in one cell. You’ll learn how to split:

• Names and Emails
• Product info (Name | Price | Category)
• Dates and Timestamps
• Any cell with commas, pipes, dashes or @ symbols

Let’s explore all six proven methods.

1️⃣ Use Flash Fill (Shortcut: CTRL + E)

Excel version: 2013 and above

🔹 How to Use:

1. Enter John (first name) in a cell next to John Doe
2. Press CTRL + E
3. Excel auto-fills the rest by detecting the pattern
4. Repeat for last name

✅ Pros:

• Super fast
• No formulas required
• Great for small datasets

2️⃣ Use Text to Columns in Excel

Best for: Delimited data (comma, pipe, tab)

Excel version: All

🔹 Steps:

1. Select the column → Go to Data > Text to Columns
2. Choose Delimited → Click Next
3. Check the delimiter (e.g., Comma or Pipe |)
4. Click Finish

Your text is now split into new columns.

✅ Pro Tips:

• Use when importing CSV or database exports
• Preview helps avoid misalignment

3️⃣ Use TEXTSPLIT Function (Excel 365 / 2021)

Best for: Multi-delimiter data

TEXTSPLIT is a modern formula that automatically separates content using multiple delimiters.

🔹 Example:

=TEXTSPLIT(A2, {“,”,”@”})

📌 For john.doe,email@example.com, this returns:

• john.doe
• email
• example.com

✅ Why it’s powerful:

• Auto spill into adjacent cells
• Handles multiple delimiters
• Great for dynamic updates

4️⃣ Use TEXTBEFORE & TEXTAFTER (Excel 365)

🔹 Example:

• First Name:

=TEXTBEFORE(A3, ",")

• Email Username:

=TEXTBEFORE(TEXTAFTER(A3, ","), "@")

• Domain:

=TEXTAFTER(A3, "@")

✅ Great for:

• Splitting names and emails
• Parsing URLs
• Working with multi-level separators

5️⃣ Use LEFT, RIGHT & MID Functions

Example: Extract first 5 characters from A2:

=LEFT(A2, 5)

🔹 Common Use Cases:

Employee IDs

• Phone number formatting
• Serial numbers

6️⃣ Bonus: Use Power Query to Split Data into Columns or Rows

Best for: Large data, automation
Target keywords: split cells Power Query, dynamic data cleaning Excel

🔹 Steps:

1. Select your data → Go to Data > Get & Transform > From Table/Range
2. In Power Query: Select the column → Click Split Column
3. Choose By Delimiter, select Comma, Space, or Custom
4. Load back to Excel

✅ Why use Power Query?

• Works well with large datasets
• Automatically refreshes with new data
• Can split into columns or rows

📊 Comparison Table of All Methods

Method	Excel Version	Best Use Case	Dynamic	Skill Level
Flash Fill	2013+	Simple name/email splits	❌	Beginner
Text to Columns	All	Delimited text (CSV, Pipe)	❌	Beginner
TEXTSPLIT	365 / 2021	Multi-delimiter dynamic splits	✅	Intermediate
TEXTBEFORE/AFTER	365	Structured data (emails, domains)	✅	Intermediate
LEFT / MID / RIGHT	All	Fixed patterns (IDs, codes)	❌	Advanced
Power Query	2016+	Large dynamic datasets	✅	Intermediate

---

🏆 Pro Tips to Boost Your Ranking and Excel Skills

• 🎥 Include a video walkthrough in your blog (or embed YouTube video)
• 📥 Offer a downloadable Excel sample file
• 📊 Add visuals: GIFs or screenshots for each method
• 🗨️ Encourage comments for video suggestions (“Want a demo of Power Query? Comment below!”)
• 🔁 Keep the blog updated for Excel 365 features

---

🧠 Final Thoughts

If you’ve been wondering how to split one cell into multiple cells in Excel, now you know 6 powerful methods — from classic tools to modern formulas and Power Query.

Each method has a place:

• ✨ Use Flash Fill for quick manual help
• 🧰 Text to Columns for old-school reliability
• 🧠 TEXTSPLIT for smart, dynamic formulas
• 🔎 TEXTBEFORE / AFTER for parsing like a pro
• 🧩 LEFT, MID, RIGHT for precision
• 🚀 Power Query for automation and speed

📚 Frequently Asked Questions (FAQ)

❓How do I split one cell into multiple cells in Excel?

Use methods like Text to Columns, Flash Fill, or formulas like TEXTSPLIT, TEXTBEFORE, and TEXTAFTER to split a single cell into multiple cells based on delimiters like commas, spaces, or symbols.

❓What is the best method to split names and emails in Excel?

Use Flash Fill for names and TEXTBEFORE/TEXTAFTER or TEXTSPLIT for splitting emails into username and domain.

❓Can I split a cell into rows instead of columns?

Yes! You can use Power Query to split a cell’s content into multiple rows by using the “Split Column > By Delimiter > Into Rows” option.

❓Is TEXTSPLIT available in all Excel versions?

No, TEXTSPLIT is available only in Excel 365 and Excel 2021. For older versions, use Text to Columns or string formulas like LEFT, RIGHT, MID.

❓Can Excel split multiple delimiters at once?

Yes, use the TEXTSPLIT function with curly brackets:

=TEXTSPLIT(A2, {“,”,”@”})

This splits based on both comma and “@” in a single formula.

Mastering Excel Conditional Formatting: A Complete Guide 2025

Mastering Excel Conditional Formatting: A Complete Guide 2025