How to Split Text to Columns Automatically with Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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


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

In order to split Text to Columns Automatically with Formulas in Excel, I am going to discuss 3 formulas. 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.

Excel Text to Columns Formula Automatically

  • In order to separate the first two digits in the city section which represents the city code, use the following formula:
=LEFT(B5,2)

Merge LEFT, MID & RIGHT Functions to Split Text to Columns Automatically

  • Press ENTER to have the output.

 Excel Text to Columns Formula Automatically

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

 Excel Text to Columns Formula Automatically

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

 Excel Text to Columns Formula Automatically

  • To have the output, press the ENTER button.

  • Finally, AutoFill the rest cells to complete the extraction.

 Excel Text to Columns Formula Automatically

Read More: How to Convert Text to Columns 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 into 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.

Combine TRANSPOSE, FILTERXML & SUBSTITUTE Functions to Split Text to Columns Automatically

  • Next, input the following formula to split the text into columns.
=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))

  • Now, ho the ENTER button to have the output.

Excel Text to Columns Formula Automatically

  • Finally, AutoFill the rest cells.

Excel Text to Columns Formula Automatically

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


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

Another effective way to split text into columns automatically with a 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 into columns.

Steps:

  • First of all, 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.
  • 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)))

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

  • To have the cell value, press ENTER.

  • Now, AutoFill along the right to split the text into columns.

Excel Text to Columns Formula Automatically

  • Finally, use Fill Handle to AutoFill the rest cells.

Excel Text to Columns Formula Automatically

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


Practice Section

For further expertise, you can practice here.

Practice Section


Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain 3 simple formulas on how to split text into columns automatically with formulas 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo