Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Split One Column into Multiple Columns in Excel Formula

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. ## 4 Easy Ways to Use Excel Formula to Split One Column into Multiple Columns

Let’s find 4 useful formulas to split one column into Multiple Columns in Excel. Checkout the formulas with explanations.

### 1. Use LEFT and RIGHT Functions to Split One Column into Multiple Columns

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. Use of 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.

### 3. Combination of LEFT, MID and RIGHT Functions

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

## Related Articles #### 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 