How to combine the text into one cell?

[clif]

How to combine the text form different cells into one cell (use comma to seperate them)?

A2 Apple
A3 Blue
A4 Cat
A5 1235


Result:

A1 Apple,Blue,Cat,1235

Thank you very much!

[Tinbendr]

In A1 put

=A2&","&A3&","&A4&","&A5 

VBA tags courtesy of www.thecodenet.com

[BrianMH]

I need to do this quite often and have created a UDF for this. It is handy when working with large numbers of cells and really reduces typing.

It could easily be modified to use something other than "," as a delimiter. You could even have it ask what to use for the delimiter simply enough.

Public Function DatatoCSV(rData As Range) As String 
    Dim c As Range 
    Dim strOut As String 
    For Each c In rData.Cells 
        If c.Value <> "" Then 
            If strOut <> "" Then 
                strOut = strOut & ", " 
            End If 
            strOut = strOut & c.Value 
        End If 
    Next 
    DatatoCSV = strOut 
     
     
End Function 

VBA tags courtesy of www.thecodenet.com

This does ignore empty cells.

[snb]

or

Function concat_snb() 
    concat_snb = Join(Filter([transpose(If(A1:A10="","~",A1:A10))], "~", False), ",") 
End Function 

VBA tags courtesy of www.thecodenet.com

or

Function concat_snb(c01 As Range) 
    concat_snb = Join(Filter(Evaluate(Replace("transpose(if(#="""", ""~"", #))", "#", c01.Address)), "~", False), ",") 
End Function 

VBA tags courtesy of www.thecodenet.com

[clif]

How to correct this code?

Public Function DatatoCSV(rData As Range) As String 
    Dim c As Range 
    Dim strOut As String 
    For Each c In rData.Cells 
        If c.Value <> "" Then 
            If strOut <> "" Then 
                strOut = strOut & ", " 
            End If 
            strOut = strOut & c.Value 
        End If 
    Next 
    DatatoCSV = strOut 
End Function 
 
Public Sub ProcessData() 
    Dim Lastrow As Long 
    Dim i As Long 
    Dim cell As Range 
    Application.ScreenUpdating = False 
    With ActiveSheet 
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        For i = Lastrow - 1 To 2 Step -1 
            If .Cells(i, "I").Value = 1 Then 
                .Cells(i, "E").Value = .Cells(i, "E").Value + .Cells(i + 1, "E").Value 
                .Cells(i, "F").Value = DatatoCSV(.Cells(i, "F"), .Cells(i + 1, "F").Value) 
                .Cells(i, "G").Value = .Cells(i, "G").Value + .Cells(i + 1, "G").Value 
                .Rows(i + 1).Delete 
            End If 
        Next i 
    End With 
    Application.ScreenUpdating = True 
End Sub 

VBA tags courtesy of www.thecodenet.com

[clif]

Anyone can help me?

[snb]

Did you overlook my suggestion ?

Just to add another one:

 
Function F_concatcol_snb(c01 As range) 
    F_concatcol_snb = Join(Application.Transpose(c01), ",") 
End Function 

VBA tags courtesy of www.thecodenet.com

If you are interested in more have a look over here.