Excel Split Address Problem. In my weekly Excel newsletter, I asked readers to use formulas to split a full address into four columns - Street Address, City, State and Zip Code. The sample data is shown below, and you can download the Excel workbook, to see the problem and the solutions.
If you’ve arrived on this page through a Google search, you will be both disappointed and pleased to know that:
- You can’t actually split cells in Excel
- But you can kind of work around it, so it looks like you’ve split a cell.
OK, so point 1 doesn’t need a lot of coverage; there just isn’t an option to “split cell” like there is in Microsoft Word. When I first came across this restriction, I had just begun working with Excel, and I was certain that I just wasn’t looking hard enough. 18 years later and I still haven’t stumbled across that feature so here’s how to work around it instead.
So let’s imagine you have made the table below, and you decide that you actually want to split column B so that instead of “Name / Surname” you have two separate columns:
Instead of splitting column B (or splitting any of the cells in column B) you need to insert a new column between columns B and C. To do this you need to select column C by clicking on the grey C at the top of the column, right clicking and then select “Insert”
You’re left with a table with a new blank column:
If you wish to then merge the column heading “Name / Surname” across column headings B and C (or any other cells for that matter), then simply select cells B1 and C1 by clicking down in cell B1 and dragging across to C1, and then in the Home ribbon click “Merge and Center”
So there you have it, it now looks as if the cells in column B have been split:
All that remains is to separate out your data into the two new columns. There is a fast way to do this (using flash fill) but that is for another tutorial. Here’s the end result:
Here’s our YouTube video on the same subject:
There’s a reason why the smallest unit in an Excel spreadsheet is called a cell: you cannot divide it any further.
But what if you want to create a master cell on top and a few (let’s say two) subordinate cells in the same row below that? Something that looks like a column header with three columns of cells beneath it:
Merging can be used to combine two or more cells to create a new, larger cell. This is how you can create a column header or label that stretches over several columns below it. A well-merged block of data is a formatting trick for a neater spreadsheet.
You can’t split cell A2 and use the divided cells for data. So, work around it by merging the upper tier cells A1, B1, and C1 into one, single cell. This single merged cell is your header cell. Now you can enter data into the individual cells below it as usual.
Merge Selected Cells
Select two or more adjacent cells you want to merge.
Click Home > Merge & Center.
If Merge and Center is dimmed, make sure you’re not editing a cell and the cells you want to merge aren’t inside a table.
To merge cells without centering, click the arrow next to Merge & Center. Then click Merge Across or Merge Cells.
Note: You can merge individual cells with data. But the contents of only one cell (The upper-left cell) is retained in the merged cell. The contents of the other cells that you merge are deleted. To change the text alignment in the merged cell, select the cell, and then click any of the alignment buttons in the Alignment group on the Home tab.
Un-Merge (Split) a Selected Cell
You can always split merged cells. If you don’t remember where you have merged cells, you can use the Find command to quickly locate merged cells.
Select the merged cell you want to unmerge.
Click Home > Merge and Center. You can also click the arrow next to Merge and Center, and then click Unmerge Cells.
The merged cells separate into individual cells. Any data in the merged cell moves to the upper-left cell when the merged cell separates.
Always Plan Ahead & Merge
![How How](/uploads/1/2/5/7/125725869/694946892.png)
Merging cells and labeling the columns improves the appearance of your spreadsheet. It is also a step towards conditional formattingAutomatically Format Data in Excel Spreadsheets With Conditional FormattingAutomatically Format Data in Excel Spreadsheets With Conditional FormattingExcel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks.Read More. But do plan beforehand to prevent data loss.
Don’t worry — you can merge cells and keep all the data too. But that takes an Excel formula and a few more steps. I am sure that little tip will follow this simpler spreadsheet task.
For now, tell us if you find this simple tip useful.
Explore more about: Microsoft Excel, Spreadsheet.