You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
(First published in April 2011, this post remains very popular with customers.We thought others would like to know about it, too, so today we're moving it back to the top slot on the Excel blog home page.)
The title of this post sounds simple. Should be easy to do, right? But customers tell us that it's very confusing and not at all clear where to find the features to get the results they want.
It all depends on what you want to merge or split. For example, you may want to create a large header cell by splitting the cells below it into a number of smaller cells. I often create such headers but use a different method than you'd expect because an individual cell simply cannot be split into smaller cells. Instead, I merge several cells into one larger cell above the cells for which it will be the header. The result is exactly the same.
Other times, you might want to merge the contents of several cells into one cell. Thinking that you can just merge the cells, you quickly realize that only the contents of one cell will be displayed in a larger cell -- and that's not the result you want! Instead, you'll need to use the CONCATENATE function to quickly combine the contents of two or more cells into one cell. Don't let the fact that it's a function intimidate you -- it's not as complicated as it sounds.
As I mentioned earlier, there's no way you can split a cell. But that doesn't mean that you cannot split the contents of a cell and distribute the split portions into adjacent cells. I actually do that quite often by using the Text to Columns Wizard. It provides options for splitting the data which returns great results every time I use it.
The following table summarizes your options for merging and splitting cells or data in cells.
If you want to:
You can:
For more information, see:
Merge two or more cells into one large cell that displays the contents of the first cell
Select the cells, click the Merge and Center button, and then use the Alignment and Wrap text buttons to display the contents of the merged cells the way you want (Home tab, Alignment group).
Video: Merge cells or split merged data
Article: Merge and unmerge cells
Merge the contents of two or more cells into one single cell
Use the CONCATENATE function to combine the content of multiple cells.
Video: Combine the contents of multiple cells into one cell
Article: Combine two or more columns by using a function
Split the contents of cells to distribute it into adjacent cells
Use the Text to Columns Wizard to split the contents of cell across adjacent cells.
Video: Use the Text to Columns Wizard
Article: Distribute the contents of a cell into adjacent columns
--Frederique Klitgaard
More info on merging and splitting cells in Excel:
Split and merge data in URLs Merge and unmerge cellsVideo: Combine the contents of multiple cells into one cell Combine two or more columns by using a functionVideo: Use the Text to Columns WizardDistribute the contents of a cell into adjacent columns
Comments: (10) Collapse
Hi Frederique,
I am migrating an Excel application from (OfficeXP SP3/OWC10) to Office 2007 SP2/OWC11) and I have run into a major roadblock with the performance of the spreadsheet control. The performance issue due merging two cells (Range.merge statement).
I know that this is a different control, but hoping you have a workaround for the merge statement.
Here is an example of the code. I can send you the complete workbook too.
Dim sRange1Start As String
Dim sRange1End As String
sRange1Start = "$C$5"
'sRange1End = "$C$6"
sRange1End = "$E$15"
form1.Spreadsheet1.Range(.Spreadsheet1.Range(sRange1Start).Address & ":" _
& .Spreadsheet1.Range(sRange1End).Address).Merge
Regards,
Saleem
Nice post Frederique!
I'd just point out that Merged cells can cause all kinds of problems, especially when you're using VBA (e.g. ClearContents). Using Format-->Cells-->Alignment-->Center Across Selection gives the same result, without merging cells.
And a shortcut to CONCATENATE is to use the Ampersand (&). I.E. =A1&" "&B1&", "&C1
Another trick is that if you're using concatenation with either method and you're using formatted numbers, you can add the TEXT function. Why? Well, let's say you wanted to concatenate a date with a string from another cell where A1 is “Today’s Date” and B1 is a date. Unfortunately, =A1&” – “&B1 will give you: “Today’s Date – 40664”, because concatenating with either method won’t convert the number’s format. So you can use: =A1&” – “&TEXT(B1,”MM/DD/YY”), resulting in “Today’s Date – 04/28/11”.
HTH,
Smitty
Saleem,
I'm assuming you're using VBA here? If so, I'm not sure why you are using a control to do this, nor why you want to use string variables. You can use the following VBA to merge that range...
Sub YOURSUBNAMEHERE()
ActiveSheet.Range("C5:E15").Merge
End Sub
I would be wary about performing this via code. While there are some reasons you might want to merge cells via code, they can be quite the headache when working with them later in code, as Smitty points out.
Zack
Hi Zack,
Yes, I am using VBA.
I am using string variables just for convenience as I was testing various scenarios. Using string varibles did not impact performance.
We have a fairly sophisticated Excel application which has many GUI controls. From Excel spreadsheets, dialogboxes appear. About 6 dialogboxes have OWC spreadsheet controls. Reason we are using merge statements via VBA is to dynamiaclly create custom spreadsheets, which have headers and footers.
The performance issue is with the OWC11 version of the spreadsheet control. There is no performance problem with OWC10 version. Also, if you merge two adjacent cells, performance is OK. If you merge three or more adjacent cells, then performance becomes are problem.
The text "wrap" function automatically adjusts height of a row to keep all the text visible, However, if you merge two or more cells, then enter text, the height doesnt adjust automatically, in fact, the double click for auto adjust always puts the cell back into default height (= one line of text).
This is not only a nuisance because each time you have to manually adjust the height, but is also dangerous because when the text is linked to another source, or if you double click for auto adjust, the cell returns to a single line height and a lot of text gets hidden. Dangerous because when your boss gets your report he's gonna whoop your butt. Or if you have sent out an application for something important, your submission can fail due to incomplete text.
This needs to be corrected pronto so that merged cells work on "auto-height" the same as unmerged cells do.
Saleem, thank you for sharing the issue you ran into when merging cells by using VBA. And thank you, Zack, for responding to the issue. I don’t know much about it, but I’ve noted the OWC11 versus OWC10 issue you mention and will run it by our VBA folks.
Smitty, I appreciate your recommendation to use Center Across Selection to avoid issues with merged cells. The Ampersand (&) method to concatenate the contents of cells is an easy way to merge cell contents. Unfortunately, the article I link to does not provide it, so thank you for sharing this information.
Paresh, I agree that the height adjustment issue is a nuisance. Thank you for pointing it out—it may help to get it fixed someday!
~Frederique
Thanks for the infromation. It was really helpful
very good
Guys I need a help. So I have 500 companies and the countries which they operate in. the data looks like this in excel
Company x country1 country2 country3 and so on like country 60 or something.
I would like the data to be transformed where I have countries on the left and then i have countries in which these companies operate.
E.g. CountryX company 1 company 2 etc
i have tried pivot table. but the number of columns are so large it is becoming a pain. Can you suggest a way to overcome?
Thanks in advance!
When you have pieces of text in cells beneath each other which you want to join, there is a nice feature to use.
Suppose you have this in cells below each other, each word in a separate cell:
The
quick
brown
fox
jumps
over
the
lazy
dog
And you want this in a single cell::
The quick brown fox jumps over the lazy dog
1. Select the cells with the words
2. Make sure the column is as wide as you expect is needed to fit the entire text
3. Select The Home tab and choose Fill, Justify.
Use the opposite to break up a sentence into words below each other, but in step 2 make sure the column is narrow.