How to Merge Two Cells in Excel without Losing Any Data

In this article, I will show you several tricks that you can use to merge two cells in Excel without losing data. Using Excel’s Merge & Center command, you cannot merge two cells in Excel without losing data. You have to use some tricks and techniques.

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).

we are going to merge the first name and last name of this data set.

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-down part of the Merge & Center command and choose Merge & Center command from the list.

Select two cells in excel; both of them have data. Then apply Merge & Center command.

Excel will show you a message like the following one.

Microsoft Excel warning message. You cannot merge cells and keep both of cells' data using the Merge and Center command.

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 are merged into one cell A2 and it is only showing the value, Rickey.

Merging with Merge & Center command will keep the first cell's data.

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.

1) Merging two cells using CONCATENATE () function

We insert a new column after the Last Name and name it as Full Name.

Insert a new column in Excel. Right click and choose the Insert command from the menu.

Name the new column heading with value Full Name.

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

Use Excel's Concatenate Function in the new column

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.

Cells are merged using Excel Concatenate function. Then copy paste the formula for other cells in the 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 column 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.

Copy and pasting the formula for other cells in the same column.

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

Select all the cells, then use CTRL + C to copy and then using CTRL + V to paste the formula in the same place

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

Choose the Values option from the CTRL drop down. It will paste the values as only values. No formula behind the scene.

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

Only the values are now showing in the column.

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.

Select the First name and Last name column and then right click. Choose the Delete command from the menu.

Here we have reached our target.

First name and last name columns are deleted. Only showing the Full name column.

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

Using AND operator to merge two cells in excel

Read more: How to Merge Text from Two Cells in Excel

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

Let’s do something like this.

merging 3 cells without losing data and adding some extra characters or spaces

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

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

merging three or more cells without no loss of data and adding some extra spaces at the end of the data

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.

Using CHAR () function will create a new line in the merged text.

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.

Read more: How to Merge Text from Two or More Cells into One Cell

Similar Readings

3) 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.

Merging cells using Excel's Flash Fill feature. Just type the first two values from the first name and last name columns.

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.

Accept the suggestions that Excel offers to you.

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.

Homework for you

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

Merge two or more cells in Excel homework. If you can do this, you're understanding this article.

Read more: How to Merge Cells in Excel with Data

4) Using Text Editor

This is also an easy technique.

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

Using Text Editor (Notepad). Copy the cells that you want to merge and then paste them in 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).

Copying TAB space from text editor

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 Spaces.

When done, click on the Replace All command button.

Using Excel Replace dialog box in Excel

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.

Using Text Editor to merge two or more cells

Now copy this data.

Copying the data from the text editor.

Paste this data into your Excel worksheet.

Paste the data that you have copied from the text editor in an Excel column.

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.

Further Readings



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

  1. Reply
    Melinda Calabrese Aug 31, 2018 at 9:35 PM

    Oops! I think you may have meant to say “Choose the entire column” in text shown below.

    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.

    Thanks for the tips

  2. Thanks so much Kawser. Will definitely try the new methods. I knew of only the concatenate function previously.

  3. Hi, Dear

    Many many Thanks for making my life easier And Inshallah ALLAH will pay you.

    You are really helping a lot. Be Blessed

Leave a reply