Excel: Merge While Keeping All Data Solved

Question

Hello,  I'm trying to merge several cells in Excel, but it deletes the values of the cells except for the first one!  Does anyone have a solution?   Thank youConfiguration: Windows XP Firefox 2.0.0.11

ouiart · Accepted Answer

I disagree with Grad; everything is possible, everything is achievable.

Concatenation: it allows the addition of strings of characters, or to be more precise, the juxtaposition of strings of characters.

Thus, the formula ="A" & "B" will give the result "AB".
Alright, but what is the use of this??

Imagine you have a table that includes, among other things, a First Name column and a Last Name column. You would like to combine the two into a single cell.

How to solve this tricky problem? Merging First Name and Last Name... the 2 cells? Try it! You didn't get the expected result? Not surprising! When merging 2 cells, Excel erases the content of the right cell, in other words, the Last Name...

The solution is to enter the following formula in cell D3:

= A3 & " " & B3

The string " " (with a space between the two quotation marks) allows you to insert a space between the First Name and the Last Name. It will give the result "A B".

grad · Answer

It is impossible to merge without deleting the data in Excel For this, you need a macro with Visual Basic (VB) that will take care of placing the contents of the merged cells side by side and putting them in the new cell, so you'll manage to avoid deleting the data from the cells when you merge them If you don't know Visual Basic, you can go here ---> link

gbinforme · Answer

Hello

Totally agree with Grad, but I would also tell you that with VBA, if you merge cells, you'll run into a lot of issues because merged cells disrupt addressing, and as soon as you've merged some cells, you'll have problems with the following ones unless you perfectly master the technique to work around the issues.

--
always zen

ego · Answer

But what about the interest? Excel is a spreadsheet, not a word processor.

cotojo · Answer

The advantage is that we can filter information based on criteria in Excel and not in Word, and that copy-pasting tables from Word to Excel is catastrophic.
I've made a little macro that merges the lines of each column one by one in a given selection....::

Here is the macro for the columns Sub FUSIONNER() ' ' FUSIONNER Macro ' ' Keyboard shortcut: Ctrl+q Dim Colonne As Long Dim Ligne As Long Dim ColonneFin As Long Dim LigneFin As Long Dim ResultCell As Variant With Selection Ligne = .Cells(1).Row LigneFin = .Cells(.Cells.Count).Row Colonne = .Cells(1).Column ColonneFin = .Cells(.Cells.Count).Column End With For i = Colonne To ColonneFin ResultCell = "" For j = Ligne To LigneFin Range(Chr(64 + i) & CStr(j)).Select ch = Chr(10) If j = LigneFin Then ch = "" ResultCell = ResultCell & ActiveCell.FormulaR1C1 & ch ActiveCell.FormulaR1C1 = "" Next j Range(Chr(64 + i) & CStr(Ligne), Chr(64 + i) & CStr(j - 1)).Merge Range(Chr(64 + i) & CStr(Ligne), Chr(64 + i) & CStr(j - 1)).WrapText = True Range(Chr(64 + i) & CStr(Ligne)).FormulaR1C1 = ResultCell Next i Range(Chr(64 + Colonne + 1) & CStr(Ligne), Chr(64 + ColonneFin) & CStr(LigneFin)).Select 'Selection.Delete Shift:=xlToLeft End Sub

gbinforme · Answer

Hello

That's great, you've accomplished what you wanted even though I still don't see the point.

On the other hand, as I pointed out to you, it's difficult to work with VBA when using merged cells and your macro no longer works if you want to add lines to merged lines, because you lose the information just like without a macro: it's one-time use code and I don't think that's the purpose of VBA...

--

always zen

Lil · Answer

Hello,  To merge the cells while keeping the content, you can use the "concat" function or do "= cell1&cell2" in a new cell and you will have all the content.

Neness · Answer

Hello,

I encountered a case where a client has 2 contracts, but when we merge and filter, it doesn't show up.

Solution: Merge a cell next to it and copy and paste the formatting, and that's it, you're good to go.

Excel: Merge While Keeping All Data

8 réponses

Texture issues in enshrouded

Pixma mx495 printer error code b204 and still under warranty

Canon mb5350 - cartridge replacement impossible

Have you ever been scammed by justlo?

Nexplay reviews

How to cancel adopte un maturé subscription

Non-functioning rj45 sockets

Sbc ru 252 universal remote code

Chicsoso reviews - is the site reliable?

My text disappears when i type on the keyboard?