How to Use Text to Columns in Excel (Split Data Easily)

Text to Columns is an Excel feature that splits the contents of one column into multiple columns based on a delimiter (comma, space, tab) or fixed character width. It is the fastest way to separate combined data like full names into first and last name columns, or CSV-formatted text into individual fields. For more Excel data techniques, see our Excel tutorial for beginners.

Key Takeaways

• Text to Columns (Data tab > Text to Columns) splits cell contents using delimiters (comma, space, tab) or fixed character positions — results overwrite adjacent columns.
• The TEXTSPLIT function (Microsoft 365 / Excel 2021+) provides a formula-based alternative that spills results dynamically and does not overwrite adjacent data.
• Flash Fill (Ctrl+E) automatically detects splitting patterns from an example you type — fastest for one-time operations but does not update when data changes.

How Do I Use Text to Columns in Excel?

Select the column containing combined data, go to Data > Text to Columns, choose Delimited or Fixed Width, select your delimiter, and click Finish — the data splits into adjacent columns.

Delimited Data (Most Common)

Use this when your data is separated by characters like commas, spaces, tabs, or semicolons.

Example: Split “Smith, John” into separate Last Name and First Name columns.

1. Select the column containing the data to split.
2. Go to Data tab > Text to Columns.
3. Select Delimited > click Next.
4. Check the delimiter(s):
5. Tab — for tab-separated data
6. Semicolon — for semicolon-separated data
7. Comma — for comma-separated data (CSV)
8. Space — for space-separated data
9. Other — type any custom delimiter
10. Preview the split in the Data preview panel.
11. Click Next to set column data formats (General, Text, Date, or skip).
12. Set the Destination cell (defaults to the selected column — change to avoid overwriting).
13. Click Finish.

Fixed Width Data

Use this when your data has consistent character spacing without delimiters.

Example: Split “20260405PROD001” where characters 1-8 = date, 9-15 = product code.

1. Select the column > Data > Text to Columns.
2. Select Fixed width > click Next.
3. Click in the Data preview to add split lines at character positions.
4. Drag lines to adjust, double-click to remove.
5. Click Next > set formats > click Finish.

Important Warning

Text to Columns overwrites adjacent columns without warning. Always ensure the columns to the right of your selected data are empty, or set a different destination cell in the wizard.

How Do I Use the TEXTSPLIT Function?

TEXTSPLIT splits text into multiple cells using a formula — results spill dynamically across columns or rows and update automatically when the source data changes.

Basic Syntax

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

Split by Comma

=TEXTSPLIT(A2, ",")

Splits “Apple,Banana,Cherry” into three cells: Apple | Banana | Cherry

Split by Space (Names)

=TEXTSPLIT(A2, " ")

Splits “John Michael Smith” into: John | Michael | Smith

Split Into Rows Instead of Columns

=TEXTSPLIT(A2, , ",")

The third argument (row_delimiter) splits vertically instead of horizontally.

Split by Multiple Delimiters

=TEXTSPLIT(A2, {","," "})

Splits by both commas and spaces simultaneously.

TEXTSPLIT Availability

Excel Version	TEXTSPLIT Available?
Microsoft 365	Yes
Excel 2021	Yes
Excel 2024	Yes
Excel 2019	No
Excel 2016	No
Excel for Web	Yes

According to Microsoft Support, TEXTSPLIT is a dynamic array function — results automatically spill into adjacent cells and update when the source changes.

How Do I Use Flash Fill to Split Data?

Type the desired result in the cell next to your data, then press Ctrl+E — Excel detects the pattern and fills the remaining cells automatically.

Step-by-Step

Example: Extract first names from “John Smith”, “Jane Doe”, “Bob Wilson”.

1. In cell B1, type the header First Name.
2. In cell B2, type the first result manually: John.
3. Click cell B3.
4. Press Ctrl+E (or go to Data > Flash Fill).
5. Excel fills B3 and below with the detected pattern: Jane, Bob, etc.
6. Repeat for Last Name in column C.

Flash Fill Strengths and Limitations

Aspect	Details
Speed	Fastest method — one example + Ctrl+E
Pattern detection	Handles complex patterns (dates, names, codes)
Auto-update	No — results are static values, not formulas
Accuracy	May produce incorrect results with inconsistent data
Best for	One-time cleanup of consistent data
Not ideal for	Data that changes frequently or has many exceptions

Flash Fill works best when your data follows a consistent pattern. If the pattern is inconsistent (e.g., some names have middle names, others do not), Flash Fill may produce errors for edge cases.

Which Method Should I Use to Split Data?

Use Text to Columns for quick one-time splits, TEXTSPLIT for formula-based dynamic splitting, and Flash Fill for pattern-based extraction when you need speed over precision.

Feature	Text to Columns	TEXTSPLIT	Flash Fill
Type	Wizard (dialog box)	Formula	Pattern detection
Dynamic	No (one-time)	Yes (auto-updates)	No (static values)
Overwrites data	Yes (adjacent columns)	No (spills safely)	No
Multiple delimiters	Yes (checkboxes)	Yes (array argument)	N/A
Fixed width	Yes	No	Possible
Availability	All Excel versions	M365 / Excel 2021+	Excel 2013+
Best for	Quick one-time splits	Ongoing data processing	Pattern extraction
Learning curve	Low (wizard)	Medium (formula)	Very low

Recommendations

• Splitting CSV imports: Text to Columns (fastest, one-time)
• Names into first/last: Flash Fill (quick, pattern-based) or TEXTSPLIT (dynamic)
• Ongoing data processing: TEXTSPLIT (formula updates automatically)
• Legacy Excel (2019 or earlier): Text to Columns or LEFT/RIGHT/MID formulas
• Complex patterns: Flash Fill first, verify results manually

How Do I Split Text Using Formulas (Legacy Method)?

For Excel versions without TEXTSPLIT, combine LEFT, RIGHT, MID, FIND, and LEN functions to extract portions of text based on delimiter positions.

Extract First Name (Before Space)

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

Finds the position of the first space and returns everything before it.

Extract Last Name (After Space)

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

Calculates the characters after the first space and returns them.

Extract Middle Value (Between Delimiters)

For “First-Middle-Last” format:

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

Formula Limitations

Issue	Impact
No delimiter found	Returns #VALUE! error
Multiple delimiters	Formulas become deeply nested
Inconsistent data	Each exception needs its own formula logic
Readability	Complex formulas are hard to maintain

Wrap formulas with IFERROR() to handle missing delimiters: =IFERROR(LEFT(A2, FIND(" ", A2) - 1), A2) returns the full cell value if no space is found.

What Are Common Text to Columns Use Cases?

Text to Columns and its alternatives solve everyday data problems across business, accounting, and data analysis workflows.

Use Case	Source Data	Split By	Method
Names	“John Smith”	Space	Flash Fill or TEXTSPLIT
CSV data	“A,B,C,D”	Comma	Text to Columns
Addresses	“123 Main St, City, ST 12345”	Comma	Text to Columns
Dates	“2026-04-05”	Hyphen	TEXTSPLIT or Text to Columns
Phone numbers	“(555) 123-4567”	Custom	Flash Fill
Email domains	“user@company.com”	@	TEXTSPLIT or formulas
Product codes	“PROD-2024-001”	Hyphen	Text to Columns
Log entries	Fixed-width server logs	Fixed Width	Text to Columns

For more Excel data manipulation techniques, see our Excel pivot table tutorial and Excel formulas reference. If you need Excel with TEXTSPLIT and dynamic arrays, Microsoft Office 2024 Professional Plus ($199.99) includes the full Excel desktop application.

Frequently Asked Questions

---