How To Split Cells In Excel

Working on a spreadsheet and wondering how to split cells in Excel? Worry not and read the article to know all the ways you can do so.

While working on Microsoft Excel, there can be several situations when you have to split a cell. These situations usually occur when you copy the data from the internet, you get it from the database or a colleague shares it with you.

One of the most common examples of splitting a cell is when you have full names and you want to split them into two cells consisting of first and last names.

The other example is when you have addresses and want to split them better to analyse the city, state and pin code. Since there are many uses of Microsoft Excel, let us now learn how to split cells.

How to Split Cells in Excel

To split a cell in Excel, add a new column, change the width of the column and merge cells. 

But to split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas.

Split a Cell in Simple Way

The following trick is used to “split” a cell in Excel.

1. For example, task B starts at 13:00 and requires 2 hours to complete.

Suppose task B starts at 13:30. We want to split cell B3 and colour the right half.

2. Now Select column C.

3. Now Right-click, and then click Insert.

The result would be:

4. Change the width of columns B and C to 32 pixels from the default width which is 64 pixels.

5. Now select cell B1 and cell C1.

6. On the Home tab, click on Alignment group, click the down arrow next to Merge & Center and click Merge Cells.

Result:

7. Just repeat steps 5-6 for cells B2 and C2 (and cells B4 and C4).

8. Change the background colour of cell B3 to No Fill.

Result:

Also Read
How to protect cells in Excel
How to merge cells in Excel
How to unmerge cells in Excel

Split Cells Using Text to Columns Feature

Let us look at another way to split cells in Excel with the Text to Columns feature. In this, you specify what separates your values in your cells, and the feature then uses that separator to split the contents of your cells.

Let us look at an example:

In the spreadsheet, click the cells you want to split into multiple cells. But do not select any column headers.

While your cells are selected, in Excel’s ribbon at the top, select “Data”.

Select the “Text to Columns” option from the “Data Tools” section in the “Data” tab.

You will see a “Text to Columns Wizard” window. Select the “Delimited” option and click “Next”.

Now, in the “Delimiters” section, select the character or characters that separate the values in your cells. In the example you can see, that the values are separated by space and a comma, so we’ll enable both “Comma” and “Space” options.

In the “Text to Columns Wizard” window, you will see what your data will look like once it splits into multiple cells. If it looks ok to you, you can click “Next.”

Now click the “Destination” field and clear its contents. Click the up-arrow icon and choose where you want to save the split data.

Click the C2 cell in the sheet to store the split data in that cell. Then click the down-arrow icon.

Back on the “Text to Columns Wizard” window, at the bottom, click “Finish.”

Now your single-cell values are split into multiple cells.

Also Read
How to wrap text in Excel
How to insert tick mark in Excel
How to convert text to number in Excel

Split Cells Using Flash Fill Feature

This is a new feature that is offered by Microsoft Excel. Instead of using the Text to Columns wizard, use flash fill which quickly splits data into multiple columns.

Let’s take the first name and the last name data:

This wizard works by identifying patterns and it replicated them for all the other cells.

Here is how to extract the first name from the list using Flash Fill:

1. In cell B2, enter the first name for Bruce Wayne (i.e., Bruce).

2. Select the cell, you’ll see a small square at the right end of the cell selection. Double click on it. The same name in all the cells will be filled.

3. Once the cells are filled, click on the autofill option that you see at the bottom right.

4. Select Flash Fill from the list.

5. Once you select Flash Fill, you’ll notice that all the cells update themselves and now show the first name for each name.

How does Flash Fill Work?

Flash Fill works on a pattern that looks in the data set and replicates. This is a great and smart feature that is useful in all cases. But there are also times when it fails.

For example, in a list of names that has a combination of names with some middle names and some without.

If you extract the middle name, Flash Fill will erroneously return the last name in case there is no first name.

Bur regardless, it is still a good tool that does most work in all use cases.

conclusion

Microsoft Excel contains all the features of spreadsheets, utilizing a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations such as mathematical operations ranging from the most fundamental, such as calculating addition, to more complex functions such as concatenation in Excel.

Easy sheet is a tech blog where you can find fantastic Excel hacks, tips, and tricks. Our goal is to make Excel more accessible to novices and to provide them with the Easy Sheet as a resource for all of their questions.

We are the finest place to go for help with anything pertaining to Microsoft Office, including Excel, Word, PowerPoint, Publisher, and more!