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.

**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. 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:**

**How to Split One Column into Multiple Columns in Excel: 7 Easy Ways****Split a Cell into Two Rows in Excel (3 ways)****How to Split Cells in Excel (The Ultimate Guide)**

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

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

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

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