How to Split Text to Columns Automatically with a Formula in Excel (3 Ways)

 

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.

Excel Text to Columns Formula Automatically

  • To separate the first two digits in the city section, enter the following formula:
=LEFT(B5,2)

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

  • Press ENTER to get the output.

 Excel Text to Columns Formula Automatically

  • Use the Fill Handle to AutoFill all cells.

  • Enter the following formula to extract the 3 digits from the middle:
=MID(B5,4,3)

 Excel Text to Columns Formula Automatically

  • Press ENTER to get the result.

  • AutoFill all the cells.

  • Enter the following formula to extract the last 3 digits:
=RIGHT(B5,3)

 Excel Text to Columns Formula Automatically

  • Press ENTER.

  • AutoFill all the cells to complete the extraction.

 Excel Text to Columns Formula Automatically

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.

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

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

Excel Text to Columns Formula Automatically

  • AutoFill all the cells.

Excel Text to Columns Formula Automatically

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

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

  • Press ENTER.

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

Excel Text to Columns Formula Automatically

  • Use Fill Handle to AutoFill all the cells.

Excel Text to Columns Formula Automatically

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


Practice Section

For further practice, you can use this dataset.

Practice Section


Download the Practice Workbook


Related Articles


<< Go Back to Excel Text to ColumnsSplitting TextSplit in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
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