Nuraida Kashmin
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.
Practice Workbook
Download the following workbook and exercise.
7 Quick Methods to Split One Column into Multiple Columns in Excel
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. Use of 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.
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.
- As a result, a Wizard Step 1 window pops up.
- Now, select the ‘Delimited’ term and click Next.
- 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.
- 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.
- As a result, we can see that the data of one column is split into multiple columns.
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.
STEPS:
- Firstly, select the column range (B5:B9) to split.
- Next, go to the Data tab >> Data Tools option >> ‘Text to Columns’ feature.
- Consequently, the Wizard Step 1 window pops up.
- From here, select the ‘Delimited’ term and click Next.
- 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.
- 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.
- Consequently, a confirmation box pops up. from here, select OK.
- At last, we can see the result.
3. Splitting up Merged Cells 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.
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.
- We can see the cells are unmerged and split into different columns.
Read More: How to Split Column in Excel Power Query (5 Easy Methods)
4. Utilization of 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.
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”.
- 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. Splitting 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.
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’.
- 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.
- From the confirmation box, select Run.
- After that, 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.
- 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. Combining 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).
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)
- Then, use Fill Handle to autofill the cells below.
- Then, select Cell E5.
- Now, write down the formula and press Enter.
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
- Then, press Enter and use Fill Handle to see the results.
7. Applying 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.
STEPS:
- Firstly, select Cell C5.
- Then type the following formula and hit Enter.
=LEFT(B5,SEARCH(" ",B5)-1)
➥ 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.
- Now, select Cell D5.
- Then type the following formula and hit Enter.
=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. 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.
