How to Split Cells in Excel (Separate into Multiple Columns)

Trying to separate cells in a spreadsheet but can’t figure out how? This guide will take you through step by step tutorials on how to split cells in a spreadsheet. Let’s dive right into all the different ways you can split cells for creating timesheets, PDFs and invoices.

Unmerge cells option

If you have an imported excel file with merged cells, try using the Unmerge Cells option by right clicking the specific cells and clicking on the  required option on top.

Diagonal Split Cell Option

You will need to use the format cells option to divide a single cell diagonally. Say you have a single cell with two words written in it. “Up” and “Down” are the words. You want to place those two words in a single cell and divide them diagonally. To begin, select the required cell and then click the right mouse button to select the Format Cells option. Under the Format Cells dialogue box, click on the Border option and then select the diagonal border and press OK.

You’ll find a diagonal line appears. Pick the first word “Up” and press the Font drop-down option under the Home tab to change the position of the words. A new dialog box with the Format Cells option will appear, highlighting only the fonts. Select the Superscript option from the Impact drop-down menu, then select the font size and style you want before pressing OK.

Following the same procedure select the second word and under the Effect, bar click on the Subscript option and then press OK.

Now, add some extra space between your words to check that they are perfectly matched. You can see the words aligned diagonally in a single cell as a result of this. This is how you can diagonally divide a single cell.

Let’s do the same thing again, but this time with the insertion of a box for a better view. Make the height and width of the targeted cell larger by dragging it, and match the 1st word on top before repeating the steps above. Select the Shapes option under the Insert tab while pressing the Alt key to insert a triangle-shaped object in that box. It will lock down the object in your cell so that its orientation is not disrupted when you drag up or down the cell. Rotate the object to your taste, then write whatever you’d like and match it to the right. The two words are divided diagonally, as you can see. The word “Up” is on the left side of the cell, and the word “Down” is on the right side. 

Text to Column Option

The Text to Columns feature in Excel allows you to break text strings using a delimiter, such as a semicolon, comma, space, or other character. Say we have a data table with details about various candidates in the participant column. We want to divide this column into three parts, with the first and last names, age, and country as the new columns. The entire process is illustrated below.

• Make three new columns titled as Last name, Age, and Country.

• Choose the cells you want to separate under the Data option on top and then click on to Text to Columns.

• The Convert Text to Columns wizard’s dialogue box will appear. Select Delimited as the file form in the first step and then click Next.

• Then, if necessary, define the Delimiters and text qualifier. Delimiters are Tab, comma, and space in this case.
• The next step is to choose a range from which to split the data. To define the selection, press the arrow icon next to the Destination box. Then hit the Finish button.

• You’ll get the exact result you’re looking for. Instead of Candidate detail, write First Name in the first column. To get a clear view, adjust the alignment to your liking.

Note: In step 1 of the Convert Text to Columns wizard box, you can also adjust the width of each column. If your table includes any dates, you can use your chosen date format in step 3.

Flash Cell Option

The Flash Fill option is available if you are using the most recent version of Microsoft Excel. Excel’s Flash Fill feature was introduced in the 2013 edition. It’s very easy to use the Flash Fill option in the 2016 update. You’ll need to add some extra columns to your main column to perform flash fill.

Our main column, in your example, contains a candidate’s name, age, and country. We’ll divide this column into four sections to display the first and last names, age, and country. The main column, as well as the other columns, are needed. You should delete the main table after you’ve finished adding data into the entire table. The process is shown below.

1. First, split the contents of one cell into multiple cells. (crown)

2. Select cell B1 and press CTRL + E (flash fill shortcut).

3. Select cell C1 and press CTRL + E.

4. Select cell D1 and press CTRL + E.

Note: flash fill in Excel only works when it recognizes a pattern. Download the Excel file and give it a try.

Formula Split Option

When using these methods, one downside is that the output does not update automatically when the source data changes. Develop formulas to get around this restriction. Let’s separate the first and last names from the full names.

1. The first name is returned by the formula below.

2. The formula below returns the last name.

3. Select the range B2:C2 and drag it down.