Excel merge cells Solved

Question

Hello everyone, I have a cell where a word is written (in Excel), and below it a cell where another word is written. I would like to merge the two cells, but when I merge them, Excel informs me that only the value in the top left corner will be kept. How can I merge two cells that each contain a word?
Thank you

JvDo · Accepted Answer

Good evening,

put this macro in a module of your workbook

Sub eliot() Set vzone = Selection vtxt = vzone.Range("a1").Value For i = 2 To vzone.Rows.Count vtxt = vtxt & Chr(10) & vzone.Cells(i, 1).Value vzone.Cells(i, 1).ClearContents Next vzone.Range("a1").Value = vtxt With vzone .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With vzone.Range("a1").Value = vzone.Range("a1").Value End Sub

Select your 4 cells and run it.
It concatenates with line breaks everything that is contained in the left column of your selection.
It works with any number of rows and columns you want.

It would need to be improved to properly handle gaps.

Best regards.

UsulArrakis · Answer

hello you need to use the CONCATENATE function in A1 "hello" in A2 "toto" in A3 = CONCATENATE(A1,A2) it gives in A3: "hello toto" -- (_Usul of Arrakis  Serial_Dreamer_)

incognito02 · Answer

Good evening UsulArrakis
Good evening Mick

UsulArrakis, your solution gives this bonsoirtoto

it’s better to use this: CONCATENATE(A1;" ";A2)

to create the space between bonjour and toto.

Have a good evening.

--
Nobody is perfect, but I try .....

mike · Answer

In fact, it's cell A1 and B1 and I see no concatenate function, I don't understand anything here... what exactly should I do?

incognito02 · Answer

Good evening Mike,   you click on the insert menu then function in category, you choose all, in function name, you select: concatenate  will that help?  Later!  -- Nobody is perfect, but I'm trying.....

mike · Answer

It doesn't work at all, in cell D2 I have: Group A and in cell D3 I have: math. I want cells D2-D3-E2-E3 to merge all four, and for the block Group A and math to be centered, with Group A above math.

UsulArrakis · Answer

You're complicated!
Give us an example file of what you want on http://cjoint.com/

Basically, in your cell you want
Group A
Maths
Contents of E2 and E3

But for that, you need to go to the next line inside the cell
by placing yourself between the words Group A and Maths
and pressing Alt+Enter

Otherwise, you have to go through VBA (macro)
--
(_Usul of Arrakis
Serial_Dreamer_)

mike · Answer

""Basically, in your cell you want Group A Math Content of E2 and E3 "  NO, I want  Group A math  to be contained and centered in cells D2-D3-E2-E3 which must merge and form a single cell."

mike · Answer

but no, it's ridiculous, it only takes a small manipulation of nothing at all, but apparently no one here knows it, well that's the height of it.

JvDo · Answer

Hello,

a minimum of respect, Mike.

The people who responded to you took the time to read you, then to try to understand you, and finally to find you a solution, test it, and formalize it a bit.

Your "grotesque" is inappropriate.

I hope you will share with us the "little manipulation that nobody knows here."

mike · Answer

I believe that anyone who has to respond should take into account the difference in Excel proficiency between the responder and the requester; otherwise, I call that pure and simple snobbery. Let me be clearer about my problem:

Launch Excel

In cell D2 write: Maths
In cell D3 write: Physics

Select cells D2-D3-E2-E3

Then: Format > Cell > Alignment > Merge Cells

Error message: The selection contains multiple values. Merging cells will keep only the data from the upper-left cell.

To fix this, there's just a simple little trick, but I completely forgot it; someone must know it?

And sorry if "grotesque" may have offended anyone...

JvDo · Answer

Good evening,  glad that it suits you!  that wasn't the case for Mike...  best regards

Eve · Answer

Thank you very much, everyone! I managed to merge several cells at work thanks to you!  ....

amadius · Answer

you need to merge several cells together. For example, select the cells you want to merge; they will be highlighted, and click on the Merge and Center button located in the Formatting toolbar of Excel.
This command is also accessible via the Format menu and then Cell. A new window will then open; go to the Alignment tab and check the Merge cells box. Finally, click the OK button to apply the change.
AMADIUS

speed06 · Answer

Hello, help me
Imagine you have 2 cells in the first one "A1" you have the word 'pierre'
in the second "A2" you have another word 'dance'

You merge in any way explained here
and in all cases in "A3" if you want to retrieve the text by copying
you have the formula and not 'pierre dance'

So for me, this is not merging cells

I would like to merge to be able to recover the merged result to create another sheet because I have 850 lines to merge to avoid copying and pasting 1700 times
If someone has a solution

Thank you

speed06 · Answer

Salut, as-tu trouvé la solution pour la fusion ?

UsulArrakis · Answer

Hello speed
in A3 put =A1&" "&A2
or use the CONCATENATE function: =CONCATENATE(A1;" ";A2)
and in the Tools menu / options / "view" tab check that "formulas" is not checked
--
(_Usul of Arrakis
Serial_Dreamer_)

Zal · Answer

Small improvement to the script, very handy I might add.
One wonders what the army of engineers in Redmond does all day... it seems to me that planning for the merging of two or more cells is the very least they could do...

Sub Concatenation_Cellulle() Dim vzone As Range Dim vtxt As Variant Dim i As Long Selection.UnMerge Set vzone = Selection vtxt = "" For i = 1 To vzone.Rows.Count If Len(vzone.Cells(i, 1).Value) > 0 Then If Len(vtxt) > 0 Then vtxt = vtxt & Chr(10) & Trim(vzone.Cells(i, 1).Value) Else vtxt = Trim(vzone.Cells(i, 1).Value) End If End If vzone.Cells(i, 1).ClearContents Next vtxt = Trim(vtxt) vzone.Range("a1").Value = vtxt With vzone .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With vzone.Range("a1").Value = vzone.Range("a1").Value End Sub

MaMaT · Answer

Hello,

I created a macro that merges certain cells when executed.
My problem is that the file being processed is large, and I am merging multiple times (about 70 times).
With each merge, I receive the usual warning message,

Warning: Microsoft Excel only places the data in the upper-left corner of the selected range into the resulting merged cell. If data exists in other cells, that data will be deleted.

Do you know how to suppress this message?
With each iteration of my macro, I have to click 70 times to tell it “yes, I want to merge.”
I’m sure it’s just a line of code or a checkbox, but I haven’t been able to find it...

Thanks in advance.

jerome · Answer

Hi everyone.

Here is my problem. I grouped several columns together (from 1997 to 2004). I created a chart that includes values from these 8 columns + 3 others (2005 to 2007). When I close these 8 columns, the values from these 8 columns are automatically removed from my chart. When I reopen them, they reappear automatically. But I need to keep the values displayed at all times, even when I close a group.

Can anyone help me please?

Thank you.

Cervantes · Answer

Hello,  If I understood correctly what you want to do, I propose my solution:  Sub CellFusion()  Dim i As Integer  For i = 1 To 50  'worksheet.temp  Range("A" + CStr(i) + ":E" + CStr(i)).Select  Selection.Merge  Next i End Sub  This script merges 1 cell across 5 columns, and does that 50 times.

Excel merge cells

21 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.

Wake on lan with livebox

"freebox" is blinking (tv)

How to access the bios