Excel Formula to Split One Column into Multiple Columns (4 Examples)

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.


Practice Workbook

Download the following workbook and exercise.


4 Easy Ways to Use Excel Formula to Split One Column into Multiple Columns

1. Excel Formula with LEFT & 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.

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

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

  • 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. Excel Formula with the Combination of LEFT, MID & 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).

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.

  • 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. Excel IF Formula to Split One Column into Multiple Columns

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

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.

Read More: How to Split One Column into Multiple Columns in Excel (7 Easy Ways)


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

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

ExcelDemy
Logo