How to Use Text to Columns in Excel to Split CSV Data

To use Text to Columns in Excel, select the column holding combined values, go to Data > Text to Columns, choose Delimited (or Fixed Width), set the separator, assign each new column a data type, and click Finish. It is the tool for the most common CSV aftermath: one column that should have been three — "Ahmed Khan" in a single Name cell, "Street, City, ZIP" crammed together, product codes like "ELEC-1001-BLK" awaiting separation. (If the file has not been converted yet, the free CSV to Excel converter builds the workbook first.)

Text to Columns works on data already in the sheet — the complement to getting the delimiter right at import (see the semicolon, tab, and pipe guides for import-time splitting).

The Delimited Split (Most Common)

1. Select the column (click its letter) containing the combined values.
2. Data > Text to Columns. The wizard opens.
3. Step 1: choose Delimited > Next.
4. Step 2: tick the separator — Comma, Space, Semicolon, Tab, or Other with a custom character (e.g., - or |). The preview shows the split live. Untick everything else.
5. Step 3: click each preview column and set its format — Text for codes with leading zeros, Date with the right DMY/MDY order for dates, General for the rest. (Date handling details: CSV date-format guide.)
6. Click Finish.

The overwrite warning: Text to Columns writes into the columns to the right of the selection. If column B already holds data and you split column A into three, column B's data is destroyed. Insert blank columns first (select B:C, right-click > Insert) or work on a copy of the sheet.

The Fixed-Width Split

For data aligned in columns by position (old mainframe exports, log files):

1. Data > Text to Columns > Fixed Width > Next.
2. In the preview, click to create break lines, drag to move them, double-click to remove. Line the breaks up with the data's visual columns.
3. Set column formats as above, Finish.

Fixed width is unforgiving of ragged data — one short value shifts everything after it. Prefer Delimited whenever a real separator exists.

Everyday Split Recipes

Names with middle names or suffixes ("Mary Ann Smith Jr.") split into more pieces than expected — after splitting, concatenate the extras back (=B2&" "&C2) or handle them manually. No wizard reads naming conventions.

Advanced: Splitting with Formulas (Non-Destructive)

When you want the split without altering the original column, use formulas in adjacent columns:

=TEXTBEFORE(A2, " ")        ' first name
=TEXTAFTER(A2, " ")         ' last name
=TEXTBEFORE(A2, "-", 2)     ' up to second dash
=TEXTSPLIT(A2, ", ")        ' spills all parts at once

TEXTSPLIT spills the whole split across columns in one formula — the modern replacement for the wizard when the source must stay untouched. On older Excel, the LEFT/FIND/MID combination does the same job with more typing.

Undoing a Bad Split

• Immediately: Ctrl+Z reverses the wizard completely.
• After saving: there is no un-split button. Re-import the column from the source CSV, or recombine with =A2&", "&B2&", "&C2 (TEXTJOIN for many columns: =TEXTJOIN(", ", TRUE, A2:D2)).
• Prevention: always split a copy of the column (insert a new column, =A2 down, then split the copy). The original stays intact no matter what.

Splitting Several Columns at Once

Real CSVs rarely have just one combined column. When three columns each need splitting, the wizard gets tedious — Power Query does them all in one pass:

1. Load with Data > From Text/CSV > Transform Data.
2. Select the first combined column > Transform > Split Column > By Delimiter. Choose the delimiter and whether to split at each occurrence or the leftmost/rightmost one.
3. Repeat for each column. Every split is a recorded step.
4. Close & Load.

Flash Fill: the no-dialog alternative. Sometimes the fastest split is no split at all: type the desired result for the first two rows in the adjacent column (e.g., first names), then press Ctrl+E (Flash Fill). Excel detects the pattern and fills the rest. Flash Fill is brilliant for one-off, irregular splits ("Dr. Ahmed Khan" → "Ahmed Khan") where no single delimiter works — but it is a guess, so verify the filled rows before trusting them.

When to use which:

For the monthly file with the same three combined columns, Power Query is the only answer you will not regret — the splits replay on refresh, and a new irregularity surfaces as a visible error instead of a silently wrong column.

Splitting into rows instead of columns. Some exports pack multiple values in one cell ("red;blue;green") where each value deserves its own row. Power Query: Split Column > By Delimiter > Advanced > "Rows". The one row becomes three, other columns duplicated — the correct shape for analysis. The wizard cannot do this at all; it is a Power Query (or TEXTSPLIT + TOCOL formula) exclusive.

FAQ

Why did Text to Columns overwrite my data?

It writes the split pieces into the columns immediately right of the selection. Insert enough blank columns first, or select a destination: in step 3 of the wizard, set the Destination to an empty cell elsewhere.

Can I split by multiple delimiters at once?

Yes — tick several in step 2 (e.g., Comma and Space). Excel splits on any of them. Watch out: "Lahore, Punjab" with both ticked splits into three pieces ("Lahore", "", "Punjab") if you are not careful about consecutive delimiters — tick "Treat consecutive delimiters as one".

How do I split without losing leading zeros?

In step 3, set the affected preview columns to Text before clicking Finish. General format converts "00123" to 123 permanently. The scientific-notation guide covers the underlying type problem.

Text to Columns vs Power Query Split Column?

Text to Columns: quick, one-off, in-place. Power Query (Transform > Split Column): recorded, repeatable, handles "split at every occurrence" vs "leftmost/rightmost delimiter" with more control. For recurring files, Power Query wins.

The wizard splits on the wrong character. What now?

Cancel, inspect the raw values (a stray separator inside values is the usual cause), and either clean the values first or use TEXTBEFORE/TEXTAFTER with occurrence numbers to target the right separator.

Final Checklist

• Blank columns inserted (or Destination set) — nothing valuable to the right.
• Correct separator ticked; preview shows the intended split before Finish.
• Each new column typed: Text for codes, proper Date order for dates.
• Worked on a copy of the column — original preserved.
• For recurring splits: rebuilt in Power Query instead of repeating the wizard.

If all five checks pass, every combined value now sits in its own column — clean, typed, and ready to analyze.