How to Use Text to Columns in Excel: Step‑by‑Step Guide

1. What Text to Columns Does

The Text to Columns wizard is a built‑in Excel feature that splits the contents of a single column into two or more separate columns. You can split by a delimiter (comma, space, tab, semicolon, or any custom character) or by fixed width (specific character positions). The operation is static—once the wizard runs, the result is plain text or numbers, not a formula, so later changes to the source cell won’t automatically re‑split the data.

What you get	How it works	Typical use case
Separate columns	Excel scans each cell, finds the chosen delimiter (or counts characters for fixed‑width), and writes the pieces into adjacent cells	Turning “Last, First” into two columns
No formulas needed	One‑time transformation; the result is static data	Cleaning a CSV import
Detects data types	Numbers and dates are converted unless you set the column to Text	Keeping IDs like “00123” with leading zeros (set Text)

Delimited vs. Fixed‑Width

Method	How you define the split
Delimited	Choose one or more characters that separate the pieces (comma, space, etc.). Check Treat consecutive delimiters as one if you want multiple delimiters to be collapsed.
Fixed width	Click in the data preview to set column break lines at exact character positions. Every row is split at those positions regardless of its content.

Note: The original column is overwritten with the first split field; the remaining fields appear in the columns to the right. Insert enough blank columns beforehand, or choose a different destination cell, to avoid overwriting data.

2. Preparing Your Data

A clean, well‑organized source column reduces errors and makes the split repeatable.

2.1 Identify the column to split

Row	Full Name
1	Smith, John A.
2	Doe, Jane B.
3	Lee, Robert C.

We’ll split Full Name into Last Name, First Name, and Middle Initial.

2.2 Insert blank columns to the right

Click the header of the column immediately to the right of the data (e.g., column B).
Press Ctrl + Shift + Right Arrow to select as many columns as you’ll need (three in this example).
Right‑click and choose Insert.

Now the sheet has empty columns for the split values.

2.3 Clean unwanted characters (optional)

Issue	Quick fix
Leading/trailing spaces	`=TRIM(A2)` → copy down → paste values over the original column
Non‑breaking spaces	Ctrl + H, find `ALT+255` (or copy‑paste a NBSP), replace with a regular space
Inconsistent delimiters	Use Find & Replace to standardize them before splitting

2.4 Verify a consistent delimiter

Scan a few rows to ensure every cell uses the same delimiter (comma, semicolon, etc.). If some rows differ, standardize them first; otherwise the wizard will produce uneven columns.

2.5 Back up your worksheet

Splitting is irreversible for the selected range. It’s safest to:

Duplicate the sheet (right‑click the tab → Move or Copy… → Create a copy).
Or copy the column to a spare area and keep the original untouched.

3. Using the Text to Columns Wizard

The wizard works in all recent Excel versions (2010‑2023, including Office 365). Follow these steps:

Step	Action	What you’ll see
1	Select the source column	Click the column header (e.g., A) containing the text to split.
2	Open the wizard	Data ► Text to Columns. The Convert Text to Columns Wizard appears.
3	Choose the split type	• Delimited – for characters such as commas, spaces, tabs. • Fixed width – for exact character positions. Click Next.
4	Set delimiters or column breaks	Delimited: tick the needed delimiter(s) or choose Other and type a custom character. Fixed width: click in the preview to create break lines, drag to adjust. Click Next.
5	Define data format for each new column	Select each column in the preview and choose General, Text, Date, or Do not import column. Use Text for fields that must keep leading zeros.
6	Choose the destination	By default the wizard overwrites the original column. Click the Destination box and pick a different cell (e.g., B1) if you want to keep the source intact.
7	Finish	Click Finish. Excel instantly creates the new columns.

Quick Tips

Preview matters – The wizard shows a live preview; go back and adjust delimiters if the preview looks off.
Preserve leading zeros – Set the column format to Text for ZIP codes, product IDs, etc.
Multiple delimiters – You can tick more than one (e.g., Space and Comma) to handle mixed separators.
Undo – Press Ctrl + Z immediately if the result isn’t what you expected.

4. Common Pitfalls & How to Avoid Them

Pitfall	Why it happens	Fix
Wrong delimiter selected	The wizard defaults to Tab; using a comma‑delimited file without changing it splits incorrectly.	In Step 4, tick Other and type the correct character (e.g., `,`).
Leading/trailing spaces remain	Excel does not trim spaces after splitting.	Apply `=TRIM(B2)` in a helper column and paste values back.
Dates become text	If the source uses a locale format that differs from your system settings, Excel treats them as text.	Before splitting, set the column’s Number Format to the appropriate Date locale, or convert after the split with `=DATEVALUE()` (or a custom `DATE()` formula).
Overwrites adjacent data	Not enough empty columns to the right, so Excel warns and may truncate data.	Insert enough blank columns beforehand, or specify a different Destination cell.
Mixed data types in one column	Excel may coerce everything to the most common type, stripping leading zeros.	In Step 5, set the format of the affected column(s) to Text.

5. Advanced: Dynamic Splitting with Formulas

When the source data changes frequently, formulas provide a live solution. The following examples work in Excel 365/2021 and Google Sheets (where noted).

Goal	Excel (365/2021)	Google Sheets
Split on a single character (e.g., `;`)	`=TEXTSPLIT(A2, ";")`	`=SPLIT(A2, ";")`
Split on every space	`=TEXTSPLIT(A2, " ")`	`=SPLIT(A2, " ")`
Ignore empty pieces from repeated spaces	`=TEXTSPLIT(A2, " ", , TRUE)`	`=SPLIT(A2, " ", TRUE, TRUE)`
Return the nth segment only	`=INDEX(TEXTSPLIT(A2, ","), 1, 3)` (3rd piece)	`=INDEX(SPLIT(A2, ","), 1, 3)`
Split full names into First & Last (handles missing middle names)	`=LET(full,A2, parts,TEXTSPLIT(full, " "), first, INDEX(parts,1), last, INDEX(parts, COUNTA(parts)), CHOOSE({1,2}, first, last))`	`=ARRAYFORMULA({INDEX(SPLIT(A2, " "), ,1), INDEX(SPLIT(A2, " "), , COUNTA(SPLIT(A2, " ")))})`

Example: Dynamic CSV parsing in Excel

A2: "John,Smith,35,NY"

=TEXTSPLIT(A2, ",")

Result

B	C	D	E
John	Smith	35	NY

When A2 changes, the four adjacent cells update automatically.

Example: Extract the third word in Google Sheets

A2: "The quick brown fox"

=INDEX(SPLIT(A2, " "), 1, 3)

Result: brown

6. FAQ

What is the Text to Columns feature used for in Excel?

It’s a quick way to turn a single column of delimited or fixed‑width text into multiple columns—ideal for cleaning CSV imports, separating names, dates, IDs, or any data that follows a predictable pattern.

Can I use Text to Columns on data that contains different delimiters?

The wizard expects a single delimiter (or a set of characters treated as separate delimiters). If rows use different delimiters, standardize them first with Find & Replace or use a formula‑based approach (e.g., REGEXREPLACE + SPLIT in Google Sheets).

Is there a way to reverse a Text to Columns operation?

Not directly. Since the split creates static values, you must either undo (Ctrl + Z) immediately, or re‑combine the columns with a formula such as =A2 & ", " & B2 & " " & C2 and then copy‑paste the result over the original data.

How does Text to Columns differ from the SPLIT function in Google Sheets?

Text to Columns is a one‑time, wizard‑driven operation that writes results into cells permanently. SPLIT (and its companion ARRAYFORMULA) is a formula that recalculates automatically whenever the source cell changes, making it better for dynamic data.

What should I do if Text to Columns splits data incorrectly?

First check the delimiter and preview in the wizard; adjust the delimiter or switch to Fixed width if needed. If the issue is caused by extra spaces or hidden characters, clean the source with TRIM or CLEAN before splitting, or use a formula‑based split that can ignore empty strings.

End of guide.