Skip to content
AnIntent

How to Split Text Into Columns in Excel With Text to Columns

Use Excel's Data > Text to Columns wizard to split one column into several, and set the Destination so results land where you want.

AnIntent Editorial · · 3 min read

Hands typing on a laptop displaying a data spreadsheet
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. Which Excel versions have the wizard
  2. Split a column with Data Text to Columns
  3. Split first and last names
  4. Set the Destination so you don't overwrite other data
  5. Watch for numbers with leading zeros
  6. When a formula is the better choice
Share this article

Excel's Text to Columns wizard splits one column of text, such as full names or comma-separated values, into several columns. Below are the steps from Microsoft's support page for the Convert Text to Columns Wizard, plus the one step that decides whether your other data stays safe.

Which Excel versions have the wizard

Microsoft's page lists Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016. That page does not separately describe Excel for the web or Excel for Mac, so if you are on either one, your menus may differ from what follows.

Split a column with Data > Text to Columns

These are the steps Microsoft gives for splitting on a delimiter, which is the character that separates the pieces (a comma, a space, and so on):

  1. Select the cell or column that contains the text.
  2. Select Data > Text to Columns.
  3. In the Convert Text to Columns Wizard, select Delimited > Next.
  4. Choose the Delimiters, for example Comma and Space. The Data preview window shows the result.
  5. Select Next.
  6. Choose the Destination in the worksheet. This is where the split data will appear.
  7. Select Finish.

The Data preview in step 4 is your chance to catch mistakes. If the columns in the preview don't look right, change the delimiter checkboxes before you move on.

Split first and last names

For a column like Jane Smith, select the column, run the wizard, and in step 4 tick Space as the delimiter. If your names are stored as Smith, Jane, the separator is a comma followed by a space, so look at the Data preview with Comma and Space both ticked and confirm you get the two pieces you expect.

Names often carry stray spaces that throw off a split. If yours do, clean them first with TRIM and CLEAN.

Set the Destination so you don't overwrite other data

Step 6 is the one people skip. The Destination box is where the split data will appear, according to Microsoft. A text column split into three pieces needs room for three columns, and if those cells already hold data you care about, you don't want the results landing on top of it.

The simple habit that avoids trouble:

  • Before you start, check what sits in the columns to the right of your text.
  • In step 6, click into the Destination box and pick the top cell of an empty area with enough blank columns for every piece.
  • If you're unsure, insert a few blank columns next to the source first, or work on a copy of the sheet.

Watch for numbers with leading zeros

Check those values in the Data preview and in the finished columns. If the zeros are gone, see how to keep leading zeros in Excel.

When a formula is the better choice

If your source data changes and you want the split to update with it, use a formula instead. Microsoft has a separate page on splitting text into columns with functions, and our guide to splitting text with TEXTSPLIT covers names, lists and delimiters. Microsoft also has a page on distributing the contents of a cell into adjacent columns.

Common questions

Which versions of Excel have Text to Columns?

Microsoft's support page lists Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016.

How do I split first and last names in Excel?

Select the name column, go to Data > Text to Columns, choose Delimited, tick Space as the delimiter, check the Data preview, set an empty Destination, and click Finish.

How do I control where the split columns appear?

In the wizard's Destination step, choose the cell where the split data should appear. Pick an empty area so existing data isn't in the way.

Should I use Text to Columns or a formula?

Text to Columns is a one-time conversion. If you want results that update when the source text changes, use a function-based approach such as TEXTSPLIT.

Hands typing on a laptop displaying a data spreadsheet
How-to

How to Use LET in Excel to Stop Repeating the Same Calculation

Use Excel's LET function to name a value or calculation once, reuse it inside a formula, and have Excel calculate it only one time.

· 4 min

Keep reading