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