How to Split One Column into Multiple Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. In the picture below, we have added an overview image to give an idea of what we expect to get by splitting a column into multiple ones.

The Overview image of Splitting One Column into Multiple Columns

In this section, I am going to describe 7 easy and simple methods that you can use to split one column into multiple columns in Excel. Let’s explore the methods one by one.


1. Using Text to Columns Feature to Split One Column into Multiple Columns in Excel

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 one column’s information (B5:B9) into multiple columns.

Dataset of 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.
  • From here, click on ‘Text to Columns’ from the Data Tools option.

Selecting Text to Columns Feature from Data tab

  • As a result, a Wizard Step 1 window pops up.
  • Now, select the ‘Delimited’ term and click Next.

Choosing Delimited in Convert Text to Columns Wizard

  • Next, we can see the Wizard Step 2 window. Check on the ‘Space’ box.
  • Here, we can see what the result looks like in the Data preview box.
  • Then, click Next.

Choosing SPace as Delimeter in Convert Text to Columns Wizard

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

Choosing Column data format and Destination in Convert Text to Columns Wizard

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

Results of Using Text to Columns Feature to Split One Column into Multiple Columns in Excel


2. Splitting One Column into Multiple Columns Based on Comma Delimiter

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.

Dataset of Splitting Multiple Lines of One Column into Multiple Columns in Excel

STEPS:

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

Choosing Text to Column Feature from Data Tab

  • Consequently, the Wizard Step 1 window pops up.
  • From here, select the ‘Delimited’ term and click Next.

Choosing Delimited from Convert Text to Column Wizard

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

Choosing delimiter in Convert Text to Columns Wizard

  • 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.

Convert Text to Column Wizard Step 1

  • Consequently, a confirmation box pops up. from here, select OK.

Clicking OK in Microsoft Excel Dialogue Box

  • At last, we can see the result.

Results after Splitting Multiple Lines of One Column into Multiple Columns in Excel


3. Splitting up Merged Cells 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.

Dataset for Splitting up Merged Cells as One Column into Multiple Columns in Excel

STEPS:

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

Unmerging seleceted Marged Cells

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

Results after Splitting up Merged Cells as One Column into Multiple Columns in Excel

Read More: How to Split Column in Excel Power Query 


4. Using Flash Fill Feature to Split up One Column into Multiple Columns in Excel

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.

Data set ofExcel ‘Flash Fill’ Feature to Split up One Column into Multiple Columns

STEPS:

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

Writing in cell C5 & D5

  • 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’.

Using Flash Fill to fill up cells

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

Results after Utilization of Flash Fill Feature to Split up One Column into Multiple Columns in Excel


5. Splitting One Column into Multiple Columns with Excel 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.

Dataset for Splitting 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.
  • From here, select ‘View Code’.

Opening VBA Editor by clicking View Code

  • 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.

Code for Splitting One Column into Multiple Columns with VBA

  • From the confirmation box, select Run.

Running Macro for Splitting One Column into Multiple Columns with VBA

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

Selecting Input Range

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

Entering Row Number in Splitting One Column into Multiple Columns with VBA

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

Inserting Output Ramnge in Splitting One Column into Multiple Columns with VBA

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

Results after Splitting One Column into Multiple Columns with VBA


6. Combining Excel INDEX & ROW Functions 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).

Dataset for Using INDEX Formula to Split up One Column into Multiple Columns

STEPS:

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

Using INDEX & ROW Function for Splitting One Column into Multiple Columns

  • Then, use Fill Handle to autofill the cells below.

Using Fill Handle to Split up One Column into Multiple Columns

  • Then, select Cell E5.
  • Now, write down the formula and press Enter.
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)

Combining INDEX & ROW Functions to Split up One Column into Multiple Columns

  • Then, press Enter and use Fill Handle to see the results.

Using Fill Handle for Splitting One Column into Multiple Columns with VBA


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

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.

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

STEPS:

  • Firstly, select Cell C5.
  • Then type the following formula and hit Enter.
=LEFT(B5,SEARCH(" ",B5)-1)

Applying 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.

Using Fill Handle for Splitting One Column into Multiple Columns

  • Now, select Cell D5.
  • Then type the following formula and hit Enter.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))

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

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

Using Fill Handle to split a Column into multiple columns

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.


Download Practice Workbook

Download the following workbook and exercise.


Conclusion

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


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo