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!
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]
David
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.
-----------------------------------------
The more you learn about something the more you know you have much to learn.
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.