Microsoft Excel Webinar

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
    988
    Location
    In A1 put
    VB:
    =A2&","&A3&","&A4&","&A5 
    
    
    Formatting tags added by mark007

    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.

    VB:
    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 
    
    
    Formatting tags added by mark007

    This does ignore empty cells.
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  4. #4
    or
    VB:
    Function concat_snb() 
        concat_snb = Join(Filter([transpose(If(A1:A10="","~",A1:A10))], "~", False), ",") 
    End Function 
    
    
    Formatting tags added by mark007
    or
    VB:
    Function concat_snb(c01 As Range) 
        concat_snb = Join(Filter(Evaluate(Replace("transpose(if(#="""", ""~"", #))", "#", c01.Address)), "~", False), ",") 
    End Function 
    
    
    Formatting tags added by mark007

  5. #5
    How to correct this code?

    VB:
    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 
    
    
    Formatting tags added by mark007

  6. #6

  7. #7
    Did you overlook my suggestion ?

    Just to add another one:

    VB:
     
    Function F_concatcol_snb(c01 As range) 
        F_concatcol_snb = Join(Application.Transpose(c01), ",") 
    End Function 
    
    
    Formatting tags added by mark007
    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
  •