Merge Cells without Losing Data [Quick Tip]
Many of us face this problem.
We have some data in a few cells. Either for alignment or structure, we would like to merge the cell contents in to one big cell. But Excel wont help as it cannot merge values from all cells.
Aparna writes,
Hi Chandoo,
Can you tell me how to merge rows without losing the data in the format given below.
given data:
row 1: abcd row 2: xyz
required format :
row 1: abcd xyz
It would save a great amount of my time if i could get a solution to this !
That got me thinking, there should be a way to merge cells without losing data?!?
Of course there is. You can use simple features in Excel to do this with ease.
Select all the cells where your data is. (All the cells need to be in one area in one column).
Adjust the column width so that you can fit all contents in one cell. (basically make it wide enough)
Select Home Ribbon & Fill & Justify
Merge cells now.
That is all. The text from selected cells will be magically re-arranged in top-most cell. If you see the text spreading 2 rows, just make the column wider and repeat the process.
See this simple animation aside to understand how it works.
But wait, this technique has some limitations,
It doesnt work if the selected cells have numbers or formulas
It only works for cells in a single column, if the cells are spread across several columns, justify will not work.
It requires a lot of steps.
Shouldn’t there be a better way to merge cells without losing data?!?
Of course there is, You can write a simple macro to do this job for you.
For eg. here is the JoinAndMerge() macro I have written that works for pretty much all types of data.
Sub JoinAndMerge() ‘joins all the content in selected cells ‘and puts the resulting text in top most cell ‘then merges all cells
Dim outputText As String Const delim = ” “
On Error Resume Next
For Each cell In Selection outputText = outputText & cell.Value & delim Next cell With Selection .Clear .Cells(1).Value = outputText .Merge .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True End WithEnd Sub
Just copy this code in to a new module or add it your personal macros workbook. And you can use it to quickly merge cells without loosing data. Simple.
The code is easy to figure out if you know basic VBA. So I will not go in to detail.
What to do with all the saved time?
Simple, go read a .
PS: It is likely that you will save even more time by reading the quick tips, then what?
PPS: Simple, go play.
Share this tip with your friends
Your email address is safe with us.
Written by Chandoo
Tags: , , , , , , , , ,
Leave a Reply
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. .
Advanced Excel Tricks
Topics & Archives
Send to Email Address
Your Email Address
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.}