How to Split One Column into Multiple Columns in Excel (7 Easy Ways)

Sometimes it becomes difficult to find value from a long Excel column. So we need to split one column into multiple columns in Excel. It makes the dataset more readable and easily accessible to the correct information. In this article, we are going to see how to split one column into multiple columns through examples and explanations.


Practice Workbook

Download the following workbook and exercise.


7 Quick Methods to Split One Column into Multiple Columns in Excel

1. Excel ‘Text To Columns’ Feature to Split One Column into Multiple Columns

Excel ‘Text to Columns’ feature is a built-in feature. Assuming we have a dataset (B4:D9) of Microsoft products. We are going to split the one column’s information (B5:B9) into multiple columns.

Excel ‘Text To Columns’ Feature to Split One Column into Multiple Columns

STEPS:

  • First, select the column range (B5:B9) to split.
  • Next, go to the Data tab.
  • Click on ‘Text to Columns’ from the Data Tools option.

Excel ‘Text To Columns’ Feature to Split One Column into Multiple Columns

  • A Wizard Step 1 window pops up.
  • Now select the ‘Delimited’ term and click Next.

 

Excel ‘Text To Columns’ Feature to Split One Column into Multiple Columns

  • We can see the Wizard Step 2 window. Check on the ‘Space’ box.
  • See how the result looks like in the Data preview box.
  • Then click Next.

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

  • Finally, we can see that the data of one column is split into multiple columns.

Read More: Excel Formula to Split One Column into Multiple Columns (4 Examples)


2. Splitting Multiple Lines of One Column into Multiple Columns in Excel

With the help of the ‘Text to Columns’ feature, we can split multiple lines of one column into multiple columns in Excel. Let’s say we have a dataset (B4:D9) of Microsoft products with years in one column. We are going to split them.

Splitting Multiple Lines of One Column into Multiple Columns in Excel

STEPS:

  • Select the column range (B5:B9) to split.
  • Next, go to the Data tab >  Data Tools option > ‘Text to Columns’ feature.

Splitting Multiple Lines of One Column into Multiple Columns in Excel

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

Splitting Multiple Lines of One Column into Multiple Columns in Excel

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

Splitting Multiple Lines of One Column into Multiple Columns in Excel

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

  • A confirmation box pops up. Select OK.

  • At last, we can see the result.


3. Split up Merged Cell as One Column into Multiple Columns in Excel

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

Split up Merged Cell as One Column into Multiple Columns in Excel

STEPS:

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

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


Similar Readings


4. Excel ‘Flash Fill’ Feature to Split up One Column into Multiple Columns

Excel has some special and smart tools. ‘Flash Fill’ is one of them. Flash Fill copies the cell pattern and gives the output like that cell. Here we have a dataset of Microsoft products with years. We are going to split this one column’s data (B4:B9) into multiple columns.

Excel ‘Flash Fill’ Feature to Split up One Column into Multiple Columns

STEPS:

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

Excel ‘Flash Fill’ Feature to Split up One Column into Multiple Columns

  • Now select Cell C5 and use the Fill Handle tool to autofill the empty cells.
  • Next from the ‘Autofill option’ click on the ‘Flash Fill’.

  • Do the same for the next column and we can see the result.


5. Split One Column into Multiple Columns with VBA

Microsoft Excel Visual Basic for Application code helps us to split one column into multiple columns. Let’s say we have a dataset (B4:B14) of Microsoft Excel products for years. We are going to split this column into two columns D4 & E4.

Split One Column into Multiple Columns with VBA

STEPS:

  • First, select all the values from the column.
  • Next, go to the worksheet from the sheet tab and right-click on it.
  • Select ‘View Code

Split One Column into Multiple Columns with VBA

  • Now, a VBA Module window pops up.
  • Type the 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
  • Then click on the Run option.

Split One Column into Multiple Columns with VBA

  • From the confirmation box, select Run.

Split One Column into Multiple Columns with VBA

  • After that, select the input range and click on OK.

Split One Column into Multiple Columns with VBA

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

 

  • Here select the first cell of the new column and click OK.

 

  • Finally, we can see the result that all the values of one column are split into two.


6. Excel INDEX Formula to Split up One Column into Multiple Columns

Excel INDEX function along with the ROWS function is used for splitting up one column. Assuming we have a dataset (B4:B14). We are going to split these values of the dataset into two columns (Column1 & Column2).

Excel INDEX Formula to Split up One Column into Multiple Columns

STEPS:

  • In the beginning, select Cell D5.
  • Now type the formula:
=INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)

Excel INDEX Formula to Split up One Column into Multiple Columns

  • Hit Enter and use Fill Handle to autofill the cells below.

Excel INDEX Formula to Split up One Column into Multiple Columns

  • Then select Cell E5.
  • Write down the formula:
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)

  • Press Enter and use Fill Handle to see the results.


7. Excel LEFT & RIGHT Functions to Split One Column into Multiple Columns

Excel LEFT Function returns the leftmost characters of a text string whereas the RIGHT function in Excel helps us to extract the last characters from a text string. They both are Text Functions in Excel. Here we have a dataset (B4:B9) in one column. We are going to use text functions to split up the values from one column.

Excel LEFT & RIGHT Functions to Split One Column into Multiple Columns

STEPS:

  • Select Cell C5.
  • Then type the formula:
=LEFT(B5,SEARCH(" ",B5)-1)

Excel LEFT & RIGHT Functions to Split One Column into Multiple Columns

Formula Breakdown

SEARCH(” “,B5)

The SEARCH function will return the position of the space.

LEFT(B5,SEARCH(” “,B5)-1)

This will return the value.

  • Next, hit Enter and use the Fill Handle tool to autofill the cells.

Excel LEFT & RIGHT Functions to Split One Column into Multiple Columns

  • Now, select Cell D5.
  • Type the formula:
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))

  • At last, press Enter and use Fill Handle to see the result.

Formula Breakdown

SEARCH(” “,B5)

The SEARCH function will return the position of the space.

LEN(B5)

The LEN function will return the total number of characters.

RIGHT(B5,LEN(B5)-SEARCH(” “,B5))

This will return the value.


Conclusion

These are the quickest way to split one column into multiple columns in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo