How To Merge Cells In Excel And Keep Data

In this article, I will show y'all several tricks that yous tin can use to merge two cells in Excel without losing data. Using Excel's Merge & Heart command, you cannot merge ii cells in Excel without losing information. You have to use some tricks and techniques.

Why cannot y'all use Merge & Center command in this example?

In the epitome below, you are seeing some First names, last names, and some addresses (Address 1, Accost ii, and Address 3).

Say, I want to combine 'First Name' and 'Last Name' parts and want to make 'Full Name'. So, I select cells A2 and B2 and click on the drop-downwards part of the Merge & Center command and choose Merge & Center command from the list.

Excel will prove you a message like the following one.

If y'all click on the OK button, and then prison cell A2'southward value (upper-left) volition exist intact and prison cell B2'southward value will be discarded. The paradigm beneath shows the same. Cell A2 and B2 are merged into 1 cell A2 and information technology is only showing the value, Rickey.

This is not what we wanted.

We want to merge two or more than cells without losing information.

Let'due south see how we tin can do that.

ane) Merging two cells using CONCATENATE () function

We insert a new column after the Last Proper name and name information technology equally Full Name.

Now in cell C2, I input this formula: =CONCATENATE(A2," ",B2)

I press enter and drag down this formula for all the cells (up to in that location is data) in the column. You are seeing all the first names and concluding names are merged into a new column.

At present this new column (Full Name) holds Excel formulas, so nosotros cannot delete our First Name and Last Name cavalcade data. Let's costless upward this new column from formulas.

Choose the entire column either using the mouse or using this keyboard shortcut (CTRL + SHIFT + Down Arrow) when your cell pointer is placed at the beginning cell of the column.

Now press CTRL + C on the keyboard to copy the entire cavalcade and press CTRL + V to paste the copied values in the same identify.

Click on the CTRL dropdown and choose the Values (V) option from the Paste Values row.

So, our cells are freed up from formulas and evidence merely the values.

Now permit's delete the unnecessary columns (Get-go Name and Concluding Name columns). Select both the A and B columns, correct-click anywhere on the selected area, and cull the Delete command.

Here we accept reached our target.

Instead of CONCATENATE () office, you can also refer to cells directly like the following image.

Let'south do something similar this.

In prison cell E2 (create a new column equally Full Address), I write this formula:

<code> =CONCATENATE(B2,",", CHAR(10),"City "&C2,",",CHAR(10),"State "&D2)

In this formula, CHAR(ten) part will create a new line. You will just encounter the new lines clearly when yous will wrap-text the cell.

To get all the cells in this new format, drag down the formula in other cells.

Annotation: if you have a subscription to Office 365, so you can also employ TEXTJOIN Office to merge Excel cells.

iii) Merging cells using Excel'south Wink Fill characteristic

Flash fill feature was added to Excel in 2013 version. This is a very good tool to employ to merge cells. Permit'southward merge the beginning name and last name using Wink Fill feature.

Again, create a new column after the Final Name and name this new column equally Full Name. In the first prison cell, I type Rickey and Harmon with a space betwixt them.

Now press CTRL + E on the keyboard. Yous encounter all the cells below are filled with full names. Clicking on the Wink Fill Options drib-down, I simply accept the suggestions. There are other options on the list.

Our new column Full Name, created with Flash Fill, does non hold whatever formula. And then, deleting the Offset Proper noun and Concluding Name columns will non create any problem.

Homework for y'all

Just using the same technique, you can also create a new address column like the following one. This is homework for you. Endeavour information technology yourself.

4) Using Text Editor

This is as well an like shooting fish in a barrel technique.

Copy the columns that you want to merge and paste into a Text Editor.

In the text editor, write a TAB infinite. I mean just choose a location in the text editor and printing the TAB key on the keyboard. So re-create that TAB space. Just press SHIFT and LEFT Pointer to select the TAB space. Copy information technology (pressing CTRL + C).

At present press CTRL + H on the keyboard. Supersede dialog will announced. In the 'Find what:' field, paste the Tab space (already copied), and in the 'Supervene upon with:' field, press the Spacebar just one time. What nosotros are going to practice is: replacing all the Tab spaces with Spaces.

When done, click on the Supervene upon All control push button.