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

Last updated: August 2018

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

Dividing to columns using Text to Columns – Delimited:

This technique is specially useful when interacting with data which is delimited by  commas (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 select Shift Cells Right.

post 2
post 3

  • Now, select the whole column which has to be separated.
  • Click on Data in the ribbon 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

Thanks for reading :-)

Leave a Comment: