Joining Cells

[smarynuk]

Hello,

I have a list containing hundreds of rows and I need to join the cells in different columns.

Eg. Col A, Row 1 is "Red Maple"
Col B, Row 1 is "Acer Rubrum"
Col C, Row 1 is "60mm"

I need to join the row into one cell reading "Red Maple Acer Rubrum 60mm"

Thanks

 

[wardy0601]

Try this formula:

Excel Workbook
	A	B	C	D
1	Red Maple	Acer Rubrum	60mm	Red Maple Acer Rubrum 60mm
Sheet2

 

[Robert Mika]

Use this UDF(press Alt+F11 and add as Modul into your VBA window):

Function AConcat(a As Variant, Optional Sep As String = "") As String

    'By Harlan Grove, March 2002

    Dim Y As Variant

    If TypeOf a Is Range Then
        For Each Y In a.Cells
            AConcat = AConcat & Y.Value & Sep
        Next Y
    ElseIf IsArray(a) Then
        For Each Y In a
            AConcat = AConcat & Y & Sep
        Next Y
    Else
        AConcat = AConcat & a & Sep
    End If
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
End Function

Then

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Red Maple</td><td style=";">Acer Rubrum</td><td style=";">60mm</td><td style=";">Red Maple Acer Rubrum 60mm</td></tr></tbody></table>

Sheet6​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D1</th><td style="text-align:left">=AConcat(A1:C1," ")</td></tr></tbody></table></td></tr></tbody></table>