Merge 2 cell (or x cells) and keep text in cells

[Bodin]

[FONT=&quot]I need to work out how to merge two columns in excel, both have text within them and this can't be done manually as there are thousands of cells.
Also, i need to keep text in them.
Need 2 solutions, for rows and for columns.

Tnx,


[/FONT]

 

[frank_AL]

Bodin,

Since you don't provide specifics I will give an example of how to accomplish this.

Lets say that Columns A and B have the data you want to merge.
In cell A1 is the value "test". In cell B1 is the value "data".

You can enter this formula in any other column but for this example I put it in cell C1
=CONCATENATE(A1," - ",B1)
This would result in the following being placed in cell C1: test - data

Hope this helps. Same concept can be applied to data in Row format

 

[Bodin]

I can do that also with cell H6
=F6&" "&G6

Tnx Frank for idea how else i can do that, and what is mistake, misunderstand in my question.
This question was about merging 2 cells A1 and B1 or A1 and A2, without losing text from 1 cell (like using merge option, but then i lose text from other cell). I just ask if its possible or not. Maybe it isnt possible

Again, tnx frank for ur time and idea about concatenate (i didnt know for that)

 

[Joe4]

Can you explain a few things?

1. How is the text to be combined?
One after the other?
Anything in between them (space, comma, carriage return)?

2. Why do you want to do this? What is the "big picture" here?
I ask because depending on your answer, there may be other (better) ways of accomplishing your goal.

 

[Bodin]

1. didnt have idea (suppose it is tougher with two words with space in 1 cell)
2. I am just curious cause i just learned =F6&" "&G6, and get idea about marging 2 cells with text in them. i am engineer for mechanical maintenance and i am using excel for making tables for following works and materials i ordered for maintenance. Much time i have need for merging 2 cells with sentences in there under one order. My resolve for that was to copy text from one cell,merge cells and then paste that text with alt+enter.
Maybe i can insert d1=IF(C1="abbreviations", "text sentences", "other text sentences") (example: text needed for cell: I am mechanical engineer - abbreviations eng)
c1=eng d1=I am mechanical engineer
I will try something like that

 

[Joe4]

I think this VBA code will do what you want:

Sub CombineCols()

    Dim col1 As Long
    Dim col2 As Long
    Dim lr1 As Long
    Dim lr2 As Long
    Dim lastRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Specify the number of the columns to combine ("A"=1, "B"=2, "C"=3, etc)
    col1 = 3
    col2 = 4
    
'   Find lastRow of data
    lr1 = Cells(Rows.Count, col1).End(xlUp).Row
    lr2 = Cells(Rows.Count, col2).End(xlUp).Row
    If lr1 > lr2 Then
        lastRow = lr1
    Else
        lastRow = lr2
    End If
    
'   Loop though all rows starting on row 2 and merge values into first column
    For r = 2 To lastRow
        Cells(r, col1) = Cells(r, col1) & Chr(10) & Cells(r, col2)
    Next r
    
'   Delete second column
    Cells(1, col2).EntireColumn.Delete
    
    Application.ScreenUpdating = True
    
End Sub

 

[Fennek]

Hi,

just trial:
merge 2 cells in an empty range, used the "format painter" to transfer to the cells you want to merge. In opposite to the normal merge, the content of both cells will remain (a bit hidden).

If you find a way to do this with vba for many cells, please report this.

regards

 

[Bodin]

I think this VBA code will do what you want:

Sub CombineCols()

    Dim col1 As Long
    Dim col2 As Long
    Dim lr1 As Long
    Dim lr2 As Long
    Dim lastRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Specify the number of the columns to combine ("A"=1, "B"=2, "C"=3, etc)
    col1 = 3
    col2 = 4
    
'   Find lastRow of data
    lr1 = Cells(Rows.Count, col1).End(xlUp).Row
    lr2 = Cells(Rows.Count, col2).End(xlUp).Row
    If lr1 > lr2 Then
        lastRow = lr1
    Else
        lastRow = lr2
    End If
    
'   Loop though all rows starting on row 2 and merge values into first column
    For r = 2 To lastRow
        Cells(r, col1) = Cells(r, col1) & Chr(10) & Cells(r, col2)
    Next r
    
'   Delete second column
    Cells(1, col2).EntireColumn.Delete
    
    Application.ScreenUpdating = True
    
End Sub

When i start in GHI cells, wanna merge HI cells http://prntscr.com/hw5bno, run macro http://prntscr.com/hw5c4x, result is http://prntscr.com/hw5caa moving all cells for 1 column to left.

@Fenek, i didnt understand how to do that. When i merge cells and go to format painter, i get the same thing (deleted text from 2nd cell)