Ultimate Guide: How to Split Text into Columns in Excel (2025 Edition) | Easy Methods

In real-world scenarios, many times you need to separate columns in Excel once you combine data of two or more columns. Right? Separating text into different columns in Excel is a game-changer when you’re dealing with messy data.

Whether you’re importing CSV files, cleaning customer lists, or analyzing survey responses, knowing how to split columns properly saves you time and frustration.

In this ultimate guide, you’ll learn 6 different methods—with step-by-step instructions, screenshots, and pro tips.

Method 1: Text to Columns Wizard

Best for: Simple delimiter-based splits (names, CSV data)

Scenario: You have full names in one column:

and you want to split full name text into two columns i.e. First Name and Last Name.

Steps:

• Select your column ➔ Go to Data ➔ Click Text to Columns.

It will open the “convert text to columns wizard” dialog box.

• Choose Delimited in the dialog box ➔ Click Next.

• On the next screen, Select delimiter (Space) ➔ click Finish.

Once the “Convert text to columns wizard” dialog closes, texts will be split into C and D columns.

Pro Tip: Don’t forget to copy your data first—Text to Columns overwrites adjacent data!

For more details on Text to Columns, visit Microsoft’s official guide [here]

Method 2: Flash Fill

Best for: Fast pattern recognition (names, product codes)

Scenario: Split email addresses into username and domain.

Steps:

• Type Elizabeth.Smith in B2 ➔ Press Ctrl + E.

The same formula will be applied in the remaining rows once you press Ctrl + E.

• Type testexample-mail.com in C2 ➔ Press Ctrl + E.

Domain name text will be split in the remaining rows as well.

Pro Tip:

• Flashfill works best with consistent patterns.
• Shortcut Tip: Use Ctrl + E to activate Flash Fill instantly.

Method 3: Using Formulas (Excel Functions)

Best for: Custom and complex splits

Scenario: Split data separated by hyphens or multiple spaces.

How to Split Data Using Formulas (Step-by-Step)

Let’s understand how to split product code using the formula that uses LEFT(), MID(), and RIGHT() functions in Excel.

Formulas:

LEFT() function to split and extract the category text:

=LEFT(B2, FIND("-", B2)-1)

This formula will split the product code and extract the left part i.e. “TSHIRT”.

How it works:

Here is a detailed explanation:

• FIND(“-“, B2) finds the position of the first hyphen (-).
  • In TSHIRT-00123-RED, the first – is at position 7.
• Subtract 1 from it because you don’t want to include the hyphen itself.
  • FIND(“-“, B2)-1 = 6
• So the final formula becomes: =LEFT(B2, 6)
  • This returns the first 6 characters, which is category TSHIRT.

MID() function to split and extract Item No. text:

=MID(B2, FIND("-", B2)+1, FIND("-", B2, FIND("-", B2)+1)-FIND("-", B2)-1)

It will split & extract Item No. i.e. “00123”.

Break it down:

Here are steps to explain how it works:

• FIND(“-“, B2) finds the first hyphen, which is at position 7.
• FIND(“-“, B2)+1 → 7 + 1 = 8, so the start position for MID is 8 (right after the first hyphen).

Now you need to figure out the length of the text to extract:

• FIND(“-“, B2, FIND(“-“, B2)+1)
  • This finds the second hyphen, which is at position 13.
• FIND(“-“, B2, FIND(“-“, B2)+1)-FIND(“-“, B2)-1
  • This is 13 – 7 – 1 = 5.
• So the formula becomes: =MID(B2, 8, 5)
  • Start at character 8 and take 5 characters, which gives you item No. 00123.

RIGHT() function to split & extract Color text:

=RIGHT(B2,LEN(B2) - FIND("-", B2, FIND("-", B2) + 1))

It will split the last part color i.e. RED.

Here are the steps:

• LEN(B2) returns the length of the entire string:
  • TSHIRT-00123-RED is 17 characters.
• FIND(“-“, B2, FIND(“-“, B2)+1)
  • Find the second hyphen, which is at position 13.
• LEN(B2) – FIND(“-“, B2, FIND(“-“, B2)+1)
  • 17 – 13 = 4
• So the formula becomes: =RIGHT(B2, 4)
  • This returns the last 4 characters, which are -RED.

But if you don’t want the hyphen:

• You could adjust by subtracting 1 (optional), or clean it up: =RIGHT(B2, LEN(B2) – FIND(“-“, B2, FIND(“-“, B2)+1))

In this case, since RIGHT starts at the end and counts 4 characters, you will get the color RED.

Need a quick reference for Excel text formulas? Check out ExcelJet’s formula library [here]

Tip: You can split data based on character position, especially when there’s no consistent delimiter, by using FIND and MID functions.

Method 4: TEXTSPLIT Function (Excel 365 & Excel 2021)

Best for: Dynamic splitting with multiple delimiters

TEXTSPLIT() is a new dynamic array function in Excel 365 and Excel 2021 that allows you to split text into multiple cells based on one or more delimiters.

• It works inside formulas.
• It automatically spills into multiple cells.
• You can split by columns, rows, or both.
• It can ignore empty values.

Let us dive into practical examples to understand how to use it to split text in Excel.

Example 1: Split a Product Code into Columns

Suppose, you have a product code SHIRT-00456-GREEN and you want to split it.

Here is a formula:

=TEXTSPLIT(B2, "-")

Type this formula in the C2 cell. You will get split text string results in C2, D2, and E2 cells.

What happens?

Excel splits the text at each hyphen and spills the results into three columns.

Easy! Right?

Why it’s better:

• No need for multiple LEFT, MID, or RIGHT formulas.
• Much simpler and cleaner.

Advanced Options:

1. Ignore Empty Cells

If your text looks like this: SHIRT–GREEN

And you run: =TEXTSPLIT(B2, “-“)

The output will be: | SHIRT | (blank) | GREEN|

To resolve this issue and ignore the blanks, you can use a formula like:

=TEXTSPLIT(B2, "-", , TRUE)

Now, your output will be: | SHIRT | GREEN | as shown in below given image.

2. Case-Insensitive Split

If your delimiters vary in case, like “TShirt-00123-red”, and you want to match “RED” and “red” without worrying about the case:

=TEXTSPLIT(A2, "-", , FALSE, 1)

Example 2: Split Names into Columns (Using Space as a Delimiter)

Let’s say you have a full name, Elizabeth Smith with a space delimiter.

To do it, your formula will be:

=TEXTSPLIT(B2, " ")

Type the above formula in the C2 cell. Your result will be in C2 and D2.

As you can see in the image, the Full name Elizabeth Smith is split into Elizabeth and Smith.

Example 3: Split into Rows AND Columns

Consider you have a text like:

Name,Email|John,john@email.com|Mary,mary@email.com

Here, Notice we have:

• | (pipe) to separate rows.
• , (comma) to separate columns

This formula will do magic for you and split them into two rows and two columns:

=TEXTSPLIT(B2, ",", "|")

Type this formula in a C2 cell and you will get results like the below:

You can split data into multiple rows or columns, even by linebreak, using the TEXTSPLIT function.

Example:

=TEXTSPLIT(A2, , CHAR(10))

This splits the contents into rows wherever there’s a line break (Alt + Enter) in the cell.

Learn more about how the TEXTSPLIT function works from Microsoft’s detailed explanation [here]

Is TEXTSPLIT() Better Than Text to Columns

Yes, it is. Because it:

• Works inside formulas, no need to click through wizards.
• Supports dynamic arrays, automatically updating as your data changes.
• Can be split by multiple delimiters, vertically and horizontally.
• Handles empty cells and irregular data gracefully.
• No overwriting of adjacent columns, unlike Text to Columns.

Real-World Use Cases for TEXTSPLIT()

• Inventory Management:
  • Split product codes into categories, SKUs, and colors.

• Email Parsing:
  • Separate usernames and domains.

• Customer Data:
  • Break down addresses (street, city, zip).

• Survey Results:
  • Split responses where users select multiple answers (delimited by commas).

• Logistics:
  • Break tracking codes or shipment routes into components.

Limitations of TEXTSPLIT():

• ❌ Only available in Excel 365 and Excel 2021.
• ❌ Requires delimiters; doesn’t work well with fixed-width data (where MID/LEFT might still be better).
• ❌ Dynamic spill might overwrite data in neighboring cells.

Pro Tips:

• ✔️ Combine TEXTSPLIT() with TEXTJOIN() to recombine data after editing.
• ✔️ Use LET() with TEXTSPLIT() to simplify complex formulas.
• ✔️ Pair with FILTER() and SORT() to build dynamic tables from split data.
• ✔️ To split text by linebreak, use CHAR(10) as the delimiter:=TEXTSPLIT(A2, CHAR(10))

Method 5: Power Query (Advanced Users & Large Data Sets)

Best for: Repeatable, automated data transformations

Power Query is an ETL (Extract, Transform, Load) tool built into Excel (Excel 2010+ with add-in, and fully integrated from Excel 2016 onwards). It’s designed for importing, cleaning, transforming, and combining data efficiently—no formulas needed!

Real-World Scenario Example

You have a Product Code column like this:

SHIRT-15896-Blue
SHOE-00456-BLACK
CAP-00078-WHITE

And you want to split it into Categories, Item Numbers, and Colors, like this:

Step-by-Step: How to Split Columns in Power Query

Step 1: Load Data into Power Query

• Select your data range (including headers).
• Go to the Data tab ➔ Click From Table/Range.
  • If your data isn’t already in a table, Excel will prompt you to create one. Click OK.

Now you’re inside the Power Query Editor.

Step 2: Split the Column by Delimiter

• Select the column you want to split (Product Code in this example).
• Go to the Home tab ➔ Click Split Column ➔ Choose By Delimiter.

Now you will see the Split Column by Delimiter dialog box.

• In the Split Column by Delimiter window:
  • Select Custom delimiter and enter a hyphen: –
  • Choose Each occurrence of the delimiter (this will split into 3 columns).
• Click OK.

Your column is now split into three separate columns!
Power Query will name them Product Code.1, Product Code.2, and Product Code.3 by default.

Step 3: Rename the New Columns

• Double-click the column headers to rename them:
  • Product Code.1 ➔ Category
  • Product Code.2 ➔ Item Number
  • Product Code.3 ➔ Color

Step 4: Close and Load the Data

• Click Close & Load from the Home tab.
• Your split data will be loaded back into an Excel worksheet.

Done! You now have a clean, separate table.

New to Power Query? Explore Microsoft’s full Power Query help guide [here]

Advanced Options You Can Explore in Power Query

Here are list of advanced options that you can use while splitting text strings in Excel.

• Split by Number of Characters (fixed-width text splitting).
• Split into Rows instead of columns (great for unpivoting data).
• Remove Empty Columns/Rows automatically.
• Trim Text, Change Data Types, and Capitalize Words (all inside Power Query).
• Merge Queries after splitting if you need to combine back later.

Pro Tip for Power Query

• You can save the steps as a Query so it updates automatically whenever the source data changes.
• You can connect Power Query directly to external sources like CSV files, databases, or websites!

Power Query Benefits Over Excel Formulas

Method 6: VBA Macro (Automation for Pros)

Best for: Bulk splitting across sheets or files

We can use VBA macro in MS Excel to automate repetitive splitting tasks and process large datasets quickly. It is very useful in data batch processing across multiple sheets.

Let’s see how we can use VBA macro to split text string.

Consider we have product codes like:

TSHIRT-00123-RED  
SHOE-00456-BLACK  
CAP-00078-WHITE

And you want to split them into three separate columns:
| Category | Item Number | Color |

Let’s see how to use VBA macro to split product codes.

Step-by-Step: How VBA Macro Works to Split Columns

Step 1: Open the VBA Editor

Press Alt + F11 in Excel to open the VBA editor.

Step 2: Insert a Module

In the VBA editor, click Insert ➔ Module.

Now, you will see the module (code) window.

Step 3: Paste the split Code

Paste the below given VBA code into the module window and close it.

Sub SplitColumnByDelimiter()
    Dim cell As Range
    Dim delimiter As String
    Dim parts() As String

    ' Set your delimiter here
    delimiter = "-"

    ' Loop through each selected cell
    For Each cell In Selection
        ' Split the text based on the delimiter
        parts = Split(cell.Value, delimiter)

        ' Output to adjacent cells
        cell.Offset(0, 1).Value = parts(0) ' First part
        cell.Offset(0, 2).Value = parts(1) ' Second part
        cell.Offset(0, 3).Value = parts(2) ' Third part
    Next cell

    MsgBox "Splitting Complete!"
End Sub

Dim cell As Range
Declares a variable to loop through each cell you select.

How Macro Works (Line by Line)

Dim delimiter As String
Sets the delimiter you’re splitting by. In this example, we’re using –.

parts = Split(cell.Value, delimiter)
This splits the text in the selected cell into an array called parts() based on the hyphen.

For example:

• If the cell has TSHIRT-00123-RED,
  • parts(0) = TSHIRT
  • parts(1) = 00123
  • parts(2) = RED

cell.Offset(0, 1).Value = parts(0)
Write the first part in the cell to the right of the current cell.

• .Offset(0, 1) = same row, one column right
• .Offset(0, 2) = same row, two columns right
• .Offset(0, 3) = same row, three columns right

MsgBox “Splitting Complete!”
Displays a popup message when it’s done!

This macro loops through each selected cell and splits its contents into adjacent columns. Also, It can be extended to loop through multiple sheets, splitting data across your workbook.

Tip: In code, You can use delimiter = “,” to split by comma and delimiter = “|” to split by pipe delimiters.

Step 4: Run the Macro

Go back to Excel.

Select the cells you want to split (just the column with the combined data).

Press Alt + F8, choose SplitColumnByDelimiter, and click Run.

Done! The split values will appear in the adjacent columns.

When Should You Use VBA Macro?

You should use VBA Macro when

• You have hundreds or thousands of rows to split.
• You need a repeatable, automated solution.
• You want to avoid formulas cluttering your workbook.

Comparison: VBA vs. Power Query vs. Formulas

Troubleshooting Common Problems

Inconsistent Delimiters
Solution: Standardize using SUBSTITUTE() before splitting.

Extra Spaces
Solution: Use TRIM() or enable Ignore Blanks in TEXTSPLIT.

Overwriting Data (Text to Columns)
Solution: Always insert empty columns before splitting.

Pro Tip

• Use keyboard shortcuts like Ctrl + E for Flash Fill to save time.
• Split text based on character position if there’s no delimiter.
• Use Power Query to split data into multiple rows, not just columns.
• Always keep raw data in a separate sheet for safety.

Free Excel Practice Workbook

Download our free workbook with all the examples from this guide!
Download Here

Related Guides

• How to Combine Two Columns in Excel (Step-by-Step)
• Fixing Common Errors When Combining Columns in Excel

Split Text in Excel FAQs:

Wrapping Up

This is your ultimate guide to separating text into columns in Excel—no matter how complex your data is!

Leave a comment if you have questions regarding the usage of any function or formula to split text in Excel.