How to Split One Column into Multiple Columns in Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Split One Column into Multiple Columns in Excel Formula

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.

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

Steps:

  • First, select Cell C5.
  • Next type the formula:
=LEFT(B5,SEARCH(" ",B5)-1)

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

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.

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

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

LEFT and RIGHT Functions

  • Finally, press ENTER and use the Fill Handle tool to see the result.

LEFT and RIGHT Functions

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.

Read More: How to Split Column by First Space in Excel


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.

INDEX-ROW Formula to Split One Column into Multiple Columns in Excel

Steps:

  • Select cell D5 at first.
  • Write down the formula:
=INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)

INDEX-ROW Formula to Split One Column into Multiple Columns in Excel

  • Next press ENTER and use the Fill Handle tool to see the result.

INDEX-ROW Formula to Split One Column into Multiple Columns in Excel

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)

INDEX ROW Formula

  • Finally, hit ENTER and use the Fill Handle to autofill the below cells.

INDEX ROW Functions

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.

Read More: Split Date and Time Column in Excel


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

Excel Formula with the Combination of LEFT, MID & RIGHT Functions

STEPS:

  • In the beginning, select cell C5.
  • Next type the formula:
=LEFT(B5,3)

Excel Formula with the Combination of LEFT, MID & RIGHT Functions

  • Hit ENTER and use the Fill Handle tool to the below cells.

Excel Formula with the Combination of LEFT, MID & RIGHT Functions

  • Now select cell D5.
  • Write down the formula:
=MID(B5,4,1)

Excel Formula with the Combination of LEFT, MID & RIGHT Functions

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

LEFT, MID, RIGHT Functions

  • Again select cell E5.
  • Type the formula:
=RIGHT(B5,3)

LEFT,MID, RIGHT Functions

  • In the end, hit ENTER and use the Fill Handle tool to see the result.

LEFT, MID and RIGHT Functions


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

Excel IF Formula to Split One Column into Multiple Columns

Steps:

  • Select cell E5 at first.
  • Type the formula:
=IF(C5="Cash",D5,"N/A")

Excel IF Formula to Split One Column into Multiple Columns

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.

Excel IF Formula to Split One Column into Multiple Columns

  • Then select cell F5.
  • Type the formula:
=IF(C5="Card",D5,"N/A")

IF Function

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.

IF Function output


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo