How can i delimit and split text between cells in Excel 2016 and 2019?


Last updated: October 2019

Applicability: Excel 2019, 2016, 2013; Office 365 and standard; Windows 7, 10 and  MAC OS X.

When analyzing significant amounts of raw data in Microsoft Excel, you might find yourself in need of dividing or splitting text between different column cells. In today’s post we’ll cover three useful techniques that you will be able to use:

  • Text to Columns for delimited files
  • Text to Columns with Fixed Width
  • Un-merging cells

Read further for the procedure – which applies with minor variations to Microsoft Office versions starting 2010.

Using the Excel delimiter for splitting text

This technique is specially useful when you are working with a worksheet that contains data which is delimited by  commas (so called CSV files) , semi colons, tabs, spaces and so forth.

  • Open your Excel and open the database in which the Columns has to be separated.
  • If you have Data’s in the next cell, select the cell and right click on it.

post 1

  • Click on Insert and then go ahead and select Shift Cells Right.

post 2
post 3



  • Now, select the entire column which will need to be separated.
  • Click on Data in the ribbon and then go ahead and select Text to Columns.

post 4

  • Select Delimited and click Next.

2015-08-08 20_16_19-Convert Text to Columns Wizard - Step 1 of 3

  • Click on any one of the option in the Delimiters and select Next.
  • Say for Example I have selected Space for separation.

post 6.jpg

  • In the Column Data format Select your option if needed. It will be General as Default.
  • To select in which cell the separation has to be done, Click on Destination and select the cells.

post 7

  • After you have done with the selection, click Finish.

post 8

 

Dividing to Columns using Fixed width

  • Open your spreadsheet and select the column cell which has to be divided.
  • Again make sure no data is available next to the dividing cell.
  • In your Excel header menu, click on Data and select Text to Columns.
  • Select Fixed width and Click on Next.

 post 10.jpg

  • For creating a break line, click on desired position
  • For deleting, double click on the break line.
  • For moving, click and drag the break line.

post 9

  • In the Column Data format Select your option if needed. It will be General as Default.
  • To select in which cell the separation has to be done, Click on Destination and select the cells.
  • Click Finish after selecting your format.

post 11.png

 

Un-merging cells

  • To split merged cells, click on Merge & center and select Unmerge cells.

2015-08-23 16_53_24-Book1 - Excel

Hope it helps :-)

Leave a Comment: