Excel Hints

Results 1 to 7 of 7

Thread: How to combine the text into one cell?

  1. #1

    How to combine the text into one cell?

    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!

  2. #2
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    991
    Location
    In A1 put [vba]=A2&","&A3&","&A4&","&A5[/vba]

    David


  3. #3
    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.

  4. #4
    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]

  5. #5
    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]

  6. #6

  7. #7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •