How to How to Split Text by Delimiter in Excel in Excel - Practical Guide

Learn to split text into separate columns using delimiters like commas, spaces, or tabs. This essential skill transforms concatenated data into organized columns, enabling efficient analysis and sorting. You'll master both the Text to Columns feature and formula-based approaches for maximum flexibility.

Why This Matters

Splitting text by delimiter is critical for cleaning messy data imports and preparing datasets for analysis without losing original data structure.

Prerequisites

•Basic Excel navigation and column/row selection
•Understanding of delimiters (comma, semicolon, space, tab)

Step-by-Step Instructions

Select your data range

Click the column header or select cells containing delimited text you want to split. Ensure all data is in a single column.

Open Text to Columns wizard

Navigate to Data tab > Text to Columns (or Data > Text to Columns in older versions). Click to launch the wizard dialog.

Choose delimiter type

In Step 1 of wizard, select 'Delimited' option. In Step 2, check the box for your delimiter: Comma, Tab, Semicolon, or Space. Preview results in the lower pane.

Set column format (optional)

In Step 3, select each column and set its format (Text, General, Date, or Skip). Use Skip to exclude unwanted columns from the split.

Click Finish to apply

Click Finish button to complete the split. Data will populate into adjacent columns, replacing the original column if needed.

Alternative Methods

Using formulas (LEFT, RIGHT, MID, FIND)

Use formulas like =LEFT(A1,FIND(",",A1)-1) to extract text before the delimiter, offering more control over non-standard delimiters.

Using TEXTSPLIT function (Excel 365)

In Excel 365+, use =TEXTSPLIT(A1,",") to split text dynamically into adjacent cells with a single formula.

Flash Fill feature

Type the first few expected results in adjacent cells, then use Data > Flash Fill (or Ctrl+E) to auto-complete the pattern.

Tips & Tricks

✓Always backup your original data before using Text to Columns, as it overwrites the original column by default.
✓Preview your split result in the wizard's lower pane before clicking Finish to catch formatting issues.
✓Use the 'Data Type' option in Step 3 to prevent Excel from auto-converting values (e.g., dates being reformatted).
✓For multiple delimiters, repeat the Text to Columns process or use TEXTSPLIT formula for all delimiters at once.

Pro Tips

★Use Ctrl+Z immediately after Text to Columns if you want to undo and preserve the original unsplit data in your worksheet.
★Combine Text to Columns with filtering to split only specific rows based on criteria, then copy results back.
★For complex delimiters (multi-character), use formulas with SUBSTITUTE or REGEX functions instead of Text to Columns.
★Test your delimiter on a single cell or small sample first to ensure it's recognized correctly by Excel.

Troubleshooting

Text to Columns option is grayed out

Ensure you've selected data in a single column and that Excel isn't in edit mode. Exit edit mode by pressing Escape first.

Split data is truncated or cut off in preview

Widen the preview pane columns by dragging column borders in the wizard, or check that delimiter is correctly selected in Step 2.

Dates or numbers appear as #### after splitting

Double-click column borders to auto-fit column width, or manually widen columns by dragging the column header border.

Formula approach leaves #VALUE! errors

Verify delimiter exists in the cell using FIND/SEARCH; if not found, the formula fails. Add IFERROR wrapper for safe handling.

Related Excel Formulas

TEXTSPLIT LEFT RIGHT MID FIND SEARCH TRIM SUBSTITUTE LEN

Frequently Asked Questions

Will Text to Columns overwrite my adjacent data?

Yes, by default Text to Columns overwrites data in columns to the right of your selection. To avoid this, first insert empty columns to the right, or copy your data to a new location before splitting.

Can I split by multiple different delimiters at once?

Text to Columns allows multiple delimiter selections in one pass (e.g., both comma and space). In Excel 365, TEXTSPLIT function handles single delimiters; for complex cases, use nested formulas or repeat the wizard.

What's the difference between Text to Columns and TEXTSPLIT?

Text to Columns physically splits data into adjacent columns (destructive change), while TEXTSPLIT is a formula that creates dynamic arrays without modifying source data (Excel 365+ only).

How do I undo Text to Columns if I made a mistake?

Press Ctrl+Z immediately after applying Text to Columns to revert changes. If you've closed the file, you cannot undo; always backup before proceeding.

Can I split text in-place without moving to new columns?

Text to Columns requires adjacent columns. Use formulas (TEXTSPLIT, LEFT, MID, RIGHT) in new columns if you want to preserve the original column unchanged.