How to Convert Text to Columns in Excel — Step-by-Step Guide

When data arrives in a single column — names, addresses, CSV values — Excel's Text to Columns feature splits it across multiple columns in seconds. Here is a complete guide to every method.

Method 1: Text to Columns Wizard (Works in All Excel Versions)

This is the standard, reliable way to split text into columns in any version of Excel.

1. Select the column (or just the cells) you want to split. Important: make sure adjacent columns to the right are empty — the split data will overwrite them.
2. Go to the Data tab in the ribbon.
3. Click Text to Columns. The Convert Text to Columns Wizard opens.
4. Choose the file type:
   • Delimited — your text is separated by a character like a comma, space, or tab. Choose this for most cases.
   • Fixed Width — your text has columns at fixed character positions (e.g., old bank statements or mainframe exports).
5. Click Next.
6. For Delimited: check the delimiter(s) your data uses. Common options:
   • Tab — for .tsv or paste from a spreadsheet
   • Comma — for CSV data
   • Space — for names, addresses, or word-separated values
   • Other — type any character (e.g., pipe | or semicolon ;)
7. Check the Data preview at the bottom — vertical lines show where columns will split.
8. Click Next → set data format per column if needed → click Finish.

Excel splits the text in-place, placing each piece in adjacent columns to the right.

Example: Split "First Name, Last Name" into Two Columns

1. Your data in column A: "Alice Smith", "Bob Jones", "Carol White"
2. Select column A → Data → Text to Columns → Delimited → Next
3. Check Space as delimiter → Next → Finish
4. Column A now has first names; column B has last names.

Method 2: TEXTSPLIT Function (Excel 365 / Excel 2021+)

The newer TEXTSPLIT function splits text into an array without using the wizard, and it is dynamic — it updates when the source text changes.

=TEXTSPLIT(A1, ",")

This splits the text in A1 at every comma and spills the results across adjacent columns automatically.

Split into rows instead of columns:

=TEXTSPLIT(A1,, ",")

(Pass the delimiter as the third argument to split into rows.)

Multiple delimiters:

=TEXTSPLIT(A1, ;)

Method 3: Formulas for Simple Splits (All Excel Versions)

To split "First Last" into two cells using formulas:

• First name: =LEFT(A1, FIND(" ", A1) - 1)
• Last name: =MID(A1, FIND(" ", A1) + 1, LEN(A1))

These formulas find the space character and extract text before/after it.

Method 4: Flash Fill (Excel 2013+)

Flash Fill can automatically detect the pattern you are splitting on:

1. In the column next to your data, type the first split result manually (e.g., the first name).
2. Start typing the second row — Excel will suggest the rest of the column.
3. Press Enter to accept the suggestion, or go to Data → Flash Fill (Ctrl+E).

Flash Fill is excellent for tricky patterns that are hard to write as formulas.

Common Issues

• Data will be overwritten: Text to Columns writes into adjacent columns — always insert blank columns first if there is existing data to the right.
• Inconsistent spacing: Use TRIM() to remove extra spaces before splitting — =TRIM(A1).
• Quoted CSV fields: Text to Columns handles quoted fields (e.g., "Smith, John") — check the "Text qualifier" option and set it to a double quote.