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


<< Go Back to Excel Text to Columns | Splitting Text | Split in Excel | Learn Excel

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.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo