In our everyday life, the words Merge and Combine have almost the same meaning. However, when you’re working in Microsoft Excel, there are important differences in their meanings!
Video: Merge Cells or Combine TextThis 1-minute video shows an example of the difference between merging cells, and combining cell contents in a different cell. .
|
Merge vs Combine
The two screenshots below give you a quick summary of the differences between merge and combine. In both screenshots, there is a first name in one cell, and a last name in another cell.
If you merge those 2 cells, hoping to get the names into a single cell, you’ll be disappointed in the result!
After merging the cells, only the first name remains on the worksheet, and the last name was discarded. Oops!
Combine With Formula
Instead of merging, you can safely and successfully combine the text from the two original cells, if you use a simple formula in another cell. The next screenshot shows an example of that.
Here’s the formula that’s in cell C2, to combine the names into a full name, with a space between first and last name:
=A2 & " " & B2
More Ways to Combine Text
There are fancier formulas for combining text, like the examples in the next video, which combines text strings with formatted numbers.
This screenshot shows a few sample formula results, and the steps and formulas are in the video below.
NOTE: You can see the written steps, and get the sample file, on the How to Combine Cells page on my Contextures site.
A full written transcript is included below the video.
Video Transcript
In this video, you’ll see how to combine text and numbers and then format those numbers if necessary, so they show us proper dates or currency or other formats. This is Debra Dalgleish from Contextures.com
Simple Formula
On this worksheet, we have a column with text and a column with numbers.
In each text cell, I’ve ended with a space character. So when I combine the text and the number, there’ll be a space character between them.
I’m going to create a very simple formula.
-Start with an equal sign, then click on the first cell.
-Type an ampersand (shift 7).
-Then click on the number, press Enter.
And it nicely combined the text and the number.
Now if I point to the fill handle, and double click to copy that down, all the others are combined, but there’s no formatting.
Add Formatting
We’re going to start with an equal sign and click on the text cell, then our ampersand.Â
And I want it to show the number, but formatted.
To format the number, I’m going to use the TEXT function.
It needs a value, which is our number here, then a comma.
Then how do we want this formatted? We’re going to put something inside double quotes. We’re going to give it a text string to use, as the guide for formatting.
I would like to see the full weekday name, and to do that, I’m going to type dddd, another double quote, and close the bracket.
Press Enter, and it says, Today is Wednesday.
Another Date Format
Now in this cell, instead of seeing the weekday name, maybe I’d like to see this day-month format. So I’ll copy this cell down to give us a starting point.
And then in our formula, instead of dddd, I would like d-mmm, press Enter. And there’s the date formatted just the way we have it here
Add the Year
Now, I could copy this down to the next cell, or maybe I’d like to include the year in that one — d-mmm-yyyy
I don’t need it in the next cell. I could have it on another sheet. And I could use that in my text function instead of typing the format each time.
Click on that cell and press Enter.
More Formats
Now in this cell, I’d like currency. So in this cell I’ll type the currency format.
So the number signs mean there may or may not be a thousands or hundreds or tens, but I always want a first number and I want a comma separator.
If I copy this down, it should pick up that format.
And for the final one here, we’d like fractions. Use # ?/? and copy that down. And there’s our hours with fractions.
Get the Workbook
Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.
Avoid Merged Cells
Merged cells can cause problems in an Excel worksheet, so I try to avoid them. There are merged cell tips and warnings on the How to Merge Cells page on my Contextures site.
This video shows one of my favourite ways to avoid merging cells – use the Center Across Selection setting instead!
NOTE: There are written steps on the Excel Worksheet Tips page.
_________________
Should You Merge Cells or Combine Text in Excel
_________________