Excel Text to Columns: The Complete Convert Text to Columns Wizard Guide

If you’re exporting data into CSV or text files, this guide may come in handy too.

Excel’s Convert Text to Columns Wizard lets you take the text in one or more cells and spread it out across multiple cells. This is called parsing, and it’s the opposite of concatenating, where you combine text from two or more cells into one cell.

For example, if you have a column of full names, you can split it into separate first name and last name columns without retyping a single row.

What the Text to Columns Wizard Does

The wizard breaks a single column of text apart based on either a delimiter (a character like a comma, space, or tab that separates each piece of data) or a fixed width (splitting at the same character position on every row). It then lets you drop the results into new columns, choosing the data format for each one as it goes.

Step 1: Select the Cell or Column You Want to Split

Select the cell or column that contains the text you want to split.

Step 2: Open Text to Columns

Go to the Data tab, then click Text to Columns in the Data Tools group. This starts the Convert Text to Columns Wizard.

Step 3: Choose Delimited or Fixed Width

On the first screen, pick the option that matches your data:

• Delimited — use this when your text is separated by a character such as a comma, space, or tab (most common case, e.g. “Smith, John” or “Smith John”).
• Fixed Width — use this when every field lines up at the same column position on every row, regardless of what character separates them.

Click Next once you’ve chosen.

Step 4: Set Your Delimiters (or Column Breaks)

If you chose Delimited, check the box for whatever character separates your data — usually Space, or Comma and Space together if your text looks like “Smith, John”. Clear any boxes that don’t apply. The Data preview pane at the bottom updates live so you can confirm the split looks right before continuing.

If you chose Fixed Width instead, click in the preview ruler to add, move, or remove the column break lines until each field is separated correctly.

Click Next.

Step 5: Set the Data Format for Each New Column

Here you choose the format for your new columns, or let Excel decide automatically. To set your own format, click a column in the Data preview window, choose a format such as Text, General, or a specific date format, and repeat for each column that needs one. This matters most for columns like postcodes, phone numbers, or dates, where Excel’s automatic guess can strip leading zeros or misread the format.

Step 6: Choose Where the Split Data Goes

Click the small collapse button next to the Destination box, then select the cells in your worksheet where you want the split data to land — for a name split into first and last name, that’s two adjacent columns with the same number of rows as your original selection. Expand the dialog box again, then click Finish.

A Quicker Alternative: Flash Fill

If your split doesn’t follow a strict delimiter — for example, pulling just the first name out of “John A. Smith” — Excel’s Flash Fill (Data > Flash Fill, or Ctrl+E) can often do the job in one keystroke. Type the result you want for the first row or two, then trigger Flash Fill and Excel will detect the pattern and fill in the rest. It’s less predictable than the wizard on messy data, but it’s worth trying first on simple splits.

If you have any questions or feedback on this guide, please leave us a message below.