Nuraida Kashmin
Excel formulas help us to find value from a long column by splitting one column into multiple columns. These make the dataset more readable and easily accessible to the correct information. In this article, we are going to see how an Excel Formula is used to split one column into multiple columns through examples and explanations.
Let’s have a quick to the overview of which formulas we are using to split one column into multiple columns and relevant outputs.
Let’s find 4 useful formulas to split one column into Multiple Columns in Excel. Checkout the formulas with explanations.
1. Using LEFT and RIGHT Functions to Split One Column into Multiple Columns in Excel
In Microsoft Excel, we have some Text Functions. The RIGHT function helps us to extract the last characters from a text string whereas the LEFT Function returns the leftmost characters of a text string. Assuming we have a dataset (B4:B9) in one column. We are going to use a formula with text functions to split up the values from one column.
Steps:
- First, select Cell C5.
- Next type the formula:
=LEFT(B5,SEARCH(" ",B5)-1)
➥ Formula Breakdown
➤ SEARCH(” “,B5)
The SEARCH function will search for the space and return with the position of the space.
➤ LEFT(B5,SEARCH(” “,B5)-1)
This will extract all the values on the left and return the value.
- Now hit ENTER and use the Fill Handle to see the results in the next cells.
- Then select Cell D5.
- Type the formula:
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))
- Finally, press ENTER and use the Fill Handle tool 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 last name value.
2. Combining INDEX-ROW Formula to Split One Column into Multiple Columns in Excel
Excel INDEX function returns the value from the given range. The ROWS function is used to return the row number. For splitting up one column into multiple columns, we can use the combination of these two functions. Here we have a dataset (B4:B14). We are going to split these values of the dataset into two columns (Column1 & Column2) using the INDEX-ROW formula.
Steps:
- Select Cell D5 at first.
- Write down the formula:
=INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)
- Next press ENTER and use the Fill Handle tool to see the result.
➥ Formula Breakdown
➤ ROWS(D$5:D5)*2-1
This will return the row number.
➤ INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)
This will return the value from the range $B$5:$B$14.
- Now select Cell E5.
- Then type the formula:
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
- Finally, hit ENTER and use the Fill Handle to autofill the below cells.
➥ Formula Breakdown
➤ ROWS(E$5:E5)*2
This will return the row number.
➤ INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
This will return the value from the range $B$5:$B$14.
Similar Readings
3. Using LEFT, MID and RIGHT Functions to Split One Column into Multiple Columns in Excel
Sometimes we need to split values into multiple columns. Excel MID function pulls out the middle characters from the middle of a text string whereas the LEFT Function returns the leftmost characters of a text string and the RIGHT function helps us to extract the last characters from a text string We use a formula with the combination of Excel LEFT, MID & RIGHT functions to split one column into multiple columns. Here we have a dataset (B4:E9) of sold items. We are going to split the sold items column into three columns (CODE, SERIES, NUMBER).
STEPS:
- In the beginning, select Cell C5.
- Next type the formula:
=LEFT(B5,3)
- Hit ENTER and use the Fill Handle tool to the below cells.
- Now select Cell D5.
- Write down the formula:
=MID(B5,4,1)
- Press Enter and use the Fill Handle to see the result.
- Again select Cell E5.
- Type the formula:
=RIGHT(B5,3)
- In the end, hit ENTER and use the Fill Handle tool to see the result.
Read More: How to Split Column in Excel Power Query (5 Easy Methods)
4. Applying IF Function to Split into Multiple Columns Based on Value
Excel IF function is used to run a logical test in a given range and returns the value whether it is TRUE or FALSE. Assuming we have a dataset (B4:F8) of customer payment history. We are going to split up the AMOUNT column into two columns (CASH & CARD).
Steps:
- Select Cell E5 at first.
- Type the formula:
=IF(C5="Cash",D5,"N/A")
This formula returns the AMOUNT value which is paid in Cash in Cell E5. Otherwise, it returns ‘N/A’.
- Now press ENTER and use the Fill Handle tool to see the result.
- Then select Cell F5.
- Type the formula:
=IF(C5="Card",D5,"N/A")
This formula returns the AMOUNT value which is paid in Card in Cell F5. Otherwise, it returns ‘N/A’.
- Finally, hit Enter and use the Fill Handle tool to the below cells.
Download Practice Workbook
Download the following workbook and exercise.
Conclusion
These are the easiest way to use Excel Formula to split one column into multiple columns. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
