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

[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



This does ignore empty cells.

[snb]

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

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

[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

[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


If you are interested in more have a look over here (http://www.snb-vba.eu/VBA_UserDefinedFunctions_en.html#L8).