Using Excel’s Merge & Center command, you cannot merge two cells in Excel without losing data. You have to use some tricks and techniques.

In this article, I will show you several tricks that you can use to merge two cells in Excel without losing data.

Why cannot you use Merge & Center command in this case?

In the image below, you are seeing some First names, last names and some addresses (Address 1, Address 2, and Address 3).

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

Excel will show you a message like the following one.

If you click on the OK button, then cell A2’s value (upper-left) will be intact and cell B2’s value will be discarded. The image below shows the same. Cell A2 and B2 is merged into one cell A2 and it is only showing the value, Rickey.

This is not what we wanted.

We want to merge two or more cells without losing data.

Let’s see how we can do that.

Merging two cells using CONCATENATE () function

We insert a new Column after the Last Name and name it as 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 there is data) in the column. You are seeing all the first names and last names are merged into a new column.

Now this new column (Full Name) holds Excel formulas, so we cannot delete our First Name and Last Name column data. Let’s free up this new column from formulas.

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

Now press CTRL + C in the keyboard to copy the entire column and press CTRL + V to paste the copied values in the same place.

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

So, our cells are freed up from formulas and show only the values.

Now let’s delete the unnecessary columns (First Name and Last Name columns). Select both the A and B columns, right-click anywhere on the selected area and choose the Delete command.

Here we have reached our target.

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

Merging 3 cells with no loss of data (& adding some extra characters)

Let’s do something like this.

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

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

In this formula, CHAR(10) part will create a new line. You will only see the new lines clearly when you will wrap-text the cell.

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

Note: if you have a subscription to Office 365, then you can also use TEXTJOIN Function to merge Excel cells.

Merging cells using Excel’s Flash Fill feature

Flash fill feature was added to Excel in 2013 version. This is a very good tool to use to merge cells. Let’s merge the first name and last name using Flash Fill feature.

Again, create a new column after the Last Name and name this new column as Full Name. In the first cell, I type Rickey and Harmon with a space between them.

Now press CTRL + E on the keyboard. You see all the cells below are filled with full names. Clicking on the Flash Fill Options drop down, I just accept the suggestions. There are other options on the list.

Our new column Full Name, created with Flash Fill, does not hold any formula. So, deleting the First Name and Last Name columns will not create any problem.

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

Using Text Editor

This is also an easy technique.

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

In the text editor, write a TAB space. I mean just choose a location in the text editor and press the TAB key on the keyboard. Then copy that TAB space. Just press SHIFT and LEFT ARROW to select the TAB space. Copy it (pressing CTRL + C).

Now Press Ctrl + H on the keyboard. Replace dialog will appear. In the ‘Find what:’ field, paste the Tab space (already copied), and in the ‘Replace with:’ field, press the Spacebar just once. What we are going to do is: replacing all the Tab spaces with a Spaces.

When done, click on the Replace All command button.

Click on the Cancel button to shut down the Replace dialog and this is what we get. All the Tab spaces are replaced with spaces.

Now copy this data.

Paste this data into your Excel worksheet.

So, you’re done.

These are my techniques to merge two or more cells into one without losing any data.

Do you know any other technique? Please let me know in the comment box.

The post How to Merge Two Cells in Excel without Losing Any Data appeared first on ExcelDemy.