Text to Columns Wizard

In the previous lessons, you’ve learned how to merge data. In this lesson, you’ll learn quite the opposite. I will show you how to split data from a single column to multiple columns.

We will use the example from the previous lesson.

Convert Text to Columns Wizard

To split this data, first, you need to select cells from B3 to B6. Next, go to DATA >> Data Tools >> Text to Columns.

After the window Text to Columns Wizard will appear, choose the Delimited radio button.

Click Next.

Because words in our example are separated by spaces, select the Space check box.

Click Next.

Click the third column and select the Do not import column (skip) option. Make the same changes for the fifth and sixth column.

Click Finish.

Excel will split the data into three columns.

Change the headers to complete this example.

Excel 365 Update

  • No significant change in core functionality: Delimiters like commas, spaces, tabs, etc., can still be used to split text data into separate columns.
  • Multi-step process: The separation process now occurs within the spreadsheet itself, requiring you to follow steps like choosing the delimiter and data formatting.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.