Merge 2 cells without losing content

Hello,

I found another solution than CONCATENATE. I go through Word.

Word, unlike Excel, has a function to convert a table to text and vice versa.

The process

Copy the part of the table you want to merge into Word. Select the table, then in the layout click on convert table to text. The transition from one column to another will be represented by a tab (by default) and the transition from one row to another by an indentation (enter key).

You just need to delete the tab or indentation characters you want to achieve the desired table format. Once the characters are deleted, select all the text created and then go to insert ----> table -----> convert text to table. All that's left is to copy and paste into Excel.

That's it.

Hello
Hello Raymond

With a macro, if the cells contain only constants (not obtained by formula)
an example of merging two contiguous cells on the same line while preserving the font color

Public Sub merge2h() Dim t1 As String, t2 As String, t As String Dim coul1 As Long, coul2 As Long Dim c As Range Application.DisplayAlerts = False With Selection If .Cells.Count <> 2 Then Exit Sub If .Rows.Count <> 1 Then Exit Sub t1 = .Cells(1, 1): coul1 = .Cells(1, 1).Font.ColorIndex t2 = .Cells(1, 2): coul2 = .Cells(1, 2).Font.ColorIndex t = t1 & " " & t2 .MergeCells = True .Value = t .Characters(1, Len(t1)).Font.ColorIndex = coul1 .Characters(Len(t1) + 2, Len(t2)).Font.ColorIndex = coul2 End With Application.DisplayAlerts = True End Sub

to get started
1. AltF11 then Insert/Module
2. copy/paste the code into Module 1
3. go back to the sheet
4. assign a keyboard shortcut to the macro
Tools/Macro/Macros
select merge2h
keyboard shortcut ctrl+f (for example)
5. select the two cells to merge then ctrl+f

good continuation

You cannot merge 2 cells that both contain data.
One cell must be empty.

For your information, since I was looking for the formula:

In the case, for example, of a list of compound last names to merge them, you need to enter:
=CONCATENATE(A1;" "&B1;" "&C1)

This will allow you to gather the information in a single cell and if you want to then copy the new result, use special paste values.

Hoping this completes the tip from the first response.