How to Split One Column into Multiple Columns in Excel

Here’s an overview image of splitting a column into multiple ones.

Method 1 – Using Text to Columns to Split One Column into Multiple Columns in Excel

We have a dataset (B4:D9) of Microsoft products. We are going to split one column’s information (B5:B9) into multiple columns.

STEPS:

Select the column range (B5:B9) to split.
Go to the Data tab.
Click on ‘Text to Columns’ from the Data Tools option.

A Wizard Step 1 window pops up.
Select the ‘Delimited’ option and click Next.

You’ll see the Wizard Step 2 window. Check on the ‘Space’ box.
You can see what the result looks like in the Data preview box.
Click Next.

The Wizard Step 3 window is open. Select ‘General’ from the ‘Column data format’ option.
Select the place where you want to see the result in the Destination box.
Check if the result is showing right from the Data preview box.
Click on Finish.

The data of one column is split into multiple columns.

Method 2 – Splitting One Column into Multiple Columns Based on Comma Delimiter

We have a dataset (B4:D9) of Microsoft products with years in one column. We are going to split them.

STEPS:

Select the column range (B5:B9) to split.
Go to the Data tab , go to the Data Tools group, and select Text to Columns.

The Wizard Step 1 window pops up.
Select the ‘Delimited’ term and click Next.

From the Wizard Step 2 window, check on the ‘Other’ box and type “,” on that. Alternatively, check the Comma option.
See how the result looks like in the Data preview box.
Click Next.

From the Wizard Step 3 window, select ‘General’ from the Column data format option.
Select the place where we want to see the result in the Destination box.
Check if the result is showing right in the Data preview box.
Click on Finish.

A confirmation box pops up. Select OK.

Here’s the result.

Method 3 – Splitting up Merged Cells into Multiple Columns in Excel

We can see a column with merged cells. We are going to split the cells and convert them into multiple columns.

STEPS:

Select all the merged cells of one column.
Go to the Home tab.
Click on the Merge & Center drop-down from the Alignment section.
Select Unmerge Cells.

We can see the cells are unmerged and split into different columns.

Method 4 – Using Flash Fill to Split up One Column into Multiple Columns in Excel

We have a dataset of Microsoft products with years. We are going to split this one column’s data (B4:B9) into multiple columns.

STEPS:

Select Cell C5 and write down the product name “Microsoft Excel” in it.
Select Cell D5 and write down the year “2018”.

Select Cell C5 and use the Fill Handle tool to autofill the empty cells.
From the ‘Autofill options’ panel (in the bottom-corner) click on the ‘Flash Fill’.

Repeat for the next column.

Method 5 – Splitting One Column into Multiple Columns with Excel VBA

We have a dataset (B4:B14) of Microsoft Excel products across years. We are going to split this column into two columns, D4 and E4.

STEPS:

Select all the values from the column.
Go to the worksheet name from the sheet tab and right-click on it.
Select ‘View Code’.

A VBA Module window pops up.
Insert this code:

Sub SplitOneColumn()
Dim rng As Range
Dim InputRng As Range
Dim OutputRng As Range
Dim xRow As Integer
Dim xCol As Integer
Dim xArr As Variant
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Select Input Range :", "SplitOneColumn", InputRng.Address, Type:=8)
xRow = Application.InputBox("Enter Row Number :", "SplitOneColumn")
Set OutputRng = Application.InputBox("Select Output Range :", xTitleId, Type:=8)
Set InputRng = InputRng.Columns(1)
xCol = InputRng.Cells.Count / xRow
ReDim xArr(1 To xRow, 1 To xCol + 1)
For i = 0 To InputRng.Cells.Count - 1
xValue = InputRng.Cells(i + 1)
iRow = i Mod xRow
iCol = VBA.Int(i / xRow)
xArr(iRow + 1, iCol + 1) = xValue
Next
OutputRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr
End Sub

Click on the Run option.

From the confirmation box, select Run.

Select the input range and click on OK.

Write down the number of how many rows we want to see in the new column and select OK.

Select the first cell of the new column and click OK.

We can see the result that all the values of one column are split into two.

Method 6 – Combining Excel INDEX and ROW Functions to Split up One Column into Multiple Columns

We have a dataset (B4:B14) where values are sequentially positioned in rows. We are going to split these values of the dataset into two columns (Column1 and Column2).