How to merge cells in Excel without losing data
Excel has powerful operators you can use to help you perform different functions in your worksheet. The only operator that we are going to learn now is the ampersand (&) which serves as a text concatenation operator.
This will help you NOT lose data when merging cells and save you a lot of work.
The syntax of the & operator is very simple:
=string1 & string2 & string3 &…stringN
Let’s put this operator into action💪
Open your practice workbook. Under the Sales Representative Label, you’ll see the first and last names of the company’s Sales Representatives in separate cells.
We will use the Text Concatenation Operator to combine the First and Last Names of the Sales Representatives.
- In cell C4, type the equal sign, then click cell A4.
- Type the operator “&”
- Then click cell B4. Now the formula looks like this:
Wasn’t that amazing? 😀 The text concatenate operator concatenated the two cells. That’s just a fancy way of saying: you have combined data from different cells without having to lose data in the process.
But we are not quite finished. Full names don’t exactly look like that. Full names have space between the first and last names. We need to add another value to the concatenation: a space.
To do that, add a space between two quotation marks like this: ” ”
This is our new formula:
Fill in the remaining rows by double-clicking or dragging down the fill handle.
Amazing, right? This operator can help you can combine data in almost any format that you want👍
To give you an idea, let’s combine names in this format: Last Name, First Name
Use this formula below:
Press Enter and fill in the rest of the rows.
The text concatenation operator can be used in a wide variety of situations to combine cells in any format you want without losing your data.
The combination possibilities are limitless! You can use it to combine multiple cells (not only limited to two) and other text.
Merge cells automatically
Did you know that in some situations Microsoft Excel will help you merge cells automatically? You read that right. Automatically😊
Let’s clear any data in the Full Name column in our practice workbook.
In cell C4, type “Abigail Aalderink”. This is the concatenation result that you want.
In cell C5, type “Steve Jones”. As soon as you start typing, Excel will fill in the cell with its best guess of what you want.
It will also show you a preview of what the rest of the entire column would look like if you used that type of concatenation.
Press Enter and it will automatically fill in other rows.
You can forget typing data one by one and make use of Excel’s auto-merge feature!