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.
- 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 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.
- 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.
- Finally, AutoFill the rest cells.
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)))
- To have the cell value, press ENTER.
- Now, AutoFill along the right to split the text into 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 expertise, you can practice here.
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
- How to Use Text to Columns in Excel for Date
- [Fixed!] Excel Text to Columns Is Deleting Data
- How to Undo Text to Columns in Excel
- Excel Text to Columns Not Working
- How to Use Line Break as Delimiter in Excel Text to Columns
- How to Convert Column to Text with Delimiter in Excel
- How to Convert Text to Columns in Excel with Multiple Spaces
- How to Use Text to Columns Feature with Carriage Return in Excel
