How to split worksheet cells by half in Excel 2016 / 2019?

Applicable to: Office 2019, 2016, 2013; 365 or standard

Here’s a question that we have received from a reader who wanted to divide an Excel column vertically:

I am working on a statistical report in Microsoft Excel, using version 365. I just imported a comma separated value file  into the A column and  now need to split a column cell vertically into two “halves”. Any simple way to get that accomplished? If it helps i am using Windows 10.

Thanks for the good question. For these scenarios i typically use the Text to Column feature, which serves my purpose pretty well.

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 full 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 , 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 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

Horizontal split cells in Excel rows

Unlike when working with tables in Microsoft Word, PowerPoint and Outlook; there is no straightforward Split command for achieving horizontal splits in Excel row cells. Readers wanting to  horizontal split cells could use specific formulas to divide their table data across two or more table cells in their worksheet.

Hope it helps 🙂

Leave a Comment