How to split a column in Excel

Excel users frequently want to split one or more columns. For example to turn:

Excel split first last


Excel split column

You can do this in Excel using the Date>Text To Columns function. But it is fairly onerous and you lose the data that was there before.

However it is very easy to split columns in Easy Data Transform:

  1. Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
  2. Start Easy Data Transform. Make sure the Auto Run button is pressed in.

Auto run on

  1. Drag the Excel spreadsheet whose columns you wish to split onto Easy Data Transform. A pink input item will be added for each worksheet selected. Just select 1 worksheet for now.
  2. Ensuring the pink input item is selected, click the Split Col button in the Left pane. A blue transform item is added and selected.

Excel combine columns

In the Right pane set Column to the column you wish to split and Delimiter to the character you want to use to split the column (e.g. a space character).

You can now see that new name columns have been added to right hand side of the table. No need to ‘run’ anything.

Excel combine name columns

For more information on Split Col options see the Split Col documentation.

If you want to rename the new columns, you can use the Rename Cols transform.

When you are satisfied with the results you can output the transformed data to a new file in a range of file formats. Ensuring that the blue transform item is selected, click the To File button at the bottom of the Left pane to create a new output file (you might have to scroll down to see the button).

change Excel date format

If you need to split columns in lots of Excel sheets you can do it in a single operation using the batch processing feature.

Easy Data Transform allows you to combine multiple transforms, such as Split Col, to quickly and easily create complex data transformations. It can combine Excel columns just as easily. Easy Data Transform can input and output in multiple formats and process millions of rows.

convert Excel

Try it free now!

Windows Logo Windows Download

v1.46.5 for Windows 11 / 10 / 8 / 7 (47 MB)
Zip file version

Apple Logo Mac Download

v1.46.5 for Mac 14.x to 10.13 (79 MB)

No commitments.
You can uninstall any time.
You don't even have to give us your email address.

Questions or problems?