Method 1 – Merging LEFT, MID, & RIGHT Functions to Split Text to Columns Automatically
Steps:
- Create an organized dataset. We 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.
- To separate the first two digits in the city section, enter the following formula:
=LEFT(B5,2)
- Press ENTER to get the output.
- Use the Fill Handle to AutoFill all cells.
- Enter the following formula to extract the 3 digits from the middle:
=MID(B5,4,3)
- Press ENTER to get the result.
- AutoFill all the cells.
- Enter the following formula to extract the last 3 digits:
=RIGHT(B5,3)
- Press ENTER.
- AutoFill all the cells to complete the extraction.
Read More: How to Convert Text to Columns in Excel
Method 2 – Combining TRANSPOSE, FILTERXML & SUBSTITUTE Functions to Split Text to Columns Automatically
Steps:
- Create an organized dataset. We have kept detailed information in the Information column which I have to split into First Name, Last Name, Gender, City, and Company in order.
- Enter the following formula to split the text into columns:
=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))
- Press ENTER to get the output.
- AutoFill all the cells.
Read More: How to Convert Text to Columns Without Overwriting in Excel
Method 3 – Combining TRIM, MID & SUBSTITUTE Functions to Split Text to Columns Automatically with Formula
Steps:
- Create an organized dataset. We have kept detailed information in the Information column which I have to split into First Name, Last Name, Gender, City, and Company in order.
- Enter the following formula in cell C5:
=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),COLUMNS($B:B)*LEN($B5)-(LEN($B5)-1),LEN($B5)))
- Press ENTER.
- AutoFill along the right to split the text into columns.
- Use Fill Handle to AutoFill all the cells.
Read More: How to Convert Text to Columns with Multiple Delimiters in Excel
Practice Section
For further practice, you can use this dataset.
Download the Practice Workbook
Related Articles
- [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
<< Go Back to Splitting Text | Split in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
