Split cells in half in Excel 2016 and 365 / 2019 (vertically)

Last updated: November 2019

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

Sometimes we import significant amount of data directly into a worksheet or into the data Model (Using Power Query and Power Pivot in Excel). Typically, we import so called comma separated value files files (*.csv). When working with csv files,  we typically find ourselves in need of vertically 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  (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: