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!
Printable View
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!
In A1 put [vba]=A2&","&A3&","&A4&","&A5[/vba]
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.
[vba]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]
This does ignore empty cells.
or
[vba]Function concat_snb()
concat_snb = Join(Filter([transpose(if(A1:A10="","~",A1:A10))], "~", False), ",")
End Function[/vba]
or
[VBA]Function concat_snb(c01 As Range)
concat_snb = Join(Filter(Evaluate(Replace("transpose(if(#="""", ""~"", #))", "#", c01.Address)), "~", False), ",")
End Function[/VBA]
How to correct this code?
[vba]
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]
Anyone can help me?
Did you overlook my suggestion ?
Just to add another one:
[vba]
Function F_concatcol_snb(c01 as range)
F_concatcol_snb = Join(Application.Transpose(c01), ",")
End Function
[/vba]
If you are interested in more have a look over here.