Sometimes we face the necessity of organising text in specific columns. We can use **Text to Columns **feature, apply different formula or **VBA **code. In this article, I will try to explain 3 simple formula on **how to split text to columns automatically with formula in Excel**. I hope this article will be helpful for you if you are looking for a formula to split text to columns in Excel.

## Download Practice Workbook

## 3 Simple Ways to Split Text to Columns Automatically with Formula in Excel

In order to split Text to Columns Automatically with Formula in Excel, I am going to discuss 3 formula. You just need to follow the following procedures to execute the purpose.

### Formula 1: Merge LEFT, MID, & RIGHT Functions to Split Text to Columns Automatically

We can apply a formula merging the **LEFT**, **MID **& **RIGHT **functions to split Text to Columns automatically. Try to follow the following procedures to do so.

** Steps**:

- Create an organized dataset first. Here, I have arranged some IP addresses in the
**IP Address**column where the first two digits represent the city code, the next three digits represent the client number and the last three digits represent the department code.

- In order to separate the first two digits in the city section which represents the city code, use the following formula:

`=LEFT(B5,2)`

- Press
**ENTER**to have the output.

- Use
**Fill Handle**to**AutoFill**the rest cells.

- Similarly, apply the following formula to extract the 3 digits from the middle.

`=MID(B5,4,3)`

- Next, hit
**ENTER**to have the result.

**AutoFill**the rest cells.

- Again, apply the following formula to extract the last 3 digits.

`=RIGHT(B5,3)`

- To have the output, press the
**ENTERÂ**button.

- Finally,
**AutoFill**the rest cells to complete the extraction.

**Read More:** **How to Use Text to Columns Feature with Carriage Return in Excel**

### Formula 2: Combine TRANSPOSE, FILTERXML & SUBSTITUTE Functions to Split Text to Columns Automatically

We can also use a combination of the **TRANSPOSE**, **FILTERXML **& **SUBSTITUTE **functions to split Text to Columns automatically. Follow the following steps to split text to columns.

** Steps**:

- First, create an organized dataset. Here, I have kept detailed information in the
**Information**column which I have to split into**First Name**,**Last Name**,**Gender**,**City**, and**Company**in order.

- Next, input the following formula to split the text to columns.

`=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))`

- Now, ho the
**ENTER**button to have the output.

- Finally,
**AutoFill**the rest cells.

**Read More:** **How to Convert Text to Columns Without Overwriting in Excel**

**Similar Readings**

**How to Convert Text to Columns in Excel with Multiple Spaces****Excel Text to Columns Not Working (4 Reasons with Solutions)****How to Convert Column to Text with Delimiter in Excel**

### Formula 3: Combine TRIM, MID & SUBSTITUTE Functions to Split Text to Columns Automatically with Formula

Another effective way to split text to columns automatically with formula is to use a formula with a combination of the **TRIM**, **MID**, and **SUBSTITUTE **functions. Now, follow the following steps to split the text to columns.

** Steps**:

- First of all, create an organized dataset. Here, I have kept detailed information in the
**Information**column which I have to split in**First Name**,**Last Name**,**Gender**,**City**, and**Company**in order. - Input the following formula to have the value in cell
**C5**.

`=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),COLUMNS($B:B)*LEN($B5)-(LEN($B5)-1),LEN($B5)))`

- To have the cell value, press
**ENTER**.

- Now,
**AutoFill**along the right to split the text to columns.

- Finally, use
**Fill Handle**to**AutoFill**the rest cells.

**Read More:**** How to Convert Text to Columns with Multiple Delimiters in Excel**

**Practice Section**

For further exp[ertise, you can practice here.

## Conclusion

At the end of this article, I like to add that I have tried to explain 3 simple formula on **how to split text to columns automatically with formula in Excel**. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.