Combine data from a column to a single cell

[Deepk]

Hi team,

I want to combine data from all the cells from a column in a single cell. All the data data should be separated by as separator (of my interest like pipe, slash, comma, dot, spaces or a value ).

Input -->>

Selected column data
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]tata[/TD]
[/TR]
[TR]
[TD]bata[/TD]
[/TR]
[TR]
[TD]mata[/TD]
[/TR]
[TR]
[TD]pata[/TD]
[/TR]
[TR]
[TD]gata[/TD]
[/TR]
</tbody>[/TABLE]
macro code generate an inputbox
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]inputbox[/TD]
[TD],[/TD]
[/TR]
</tbody>[/TABLE]

Output-->>

cell in second next row after last row
tata, bata, mata, pata, gata

I hope my query is clear. Please help. Thank you

 

[Fluff]

How about

Sub JoinData()

   Dim Sep As String
   
   Sep = InputBox("Please enter separator")
   If Sep <> "" Then
      Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp))), Sep)
   End If
End Sub

 

[Joe4]

Here is a Function that will do that for you. Simply provide the range and the separator, and it will do what you want.

Function MyCombine(myRange As Range, mySep As String) As String

    Dim cell As Range
    Dim myString As String
    
    For Each cell In myRange
        myString = myString & cell & mySep
    Next cell
    
    If Len(myString) > 0 Then
        MyCombine = Left(myString, Len(myString) - 1)
    End If

End Function

So, if you wanted to do cells G1:G5 separated by a pipe symbol, your formula would just look like:
=MyCombine(G1:G5,"|")

 

[Deepk]

How about

Sub JoinData()

   Dim Sep As String
   
   Sep = InputBox("Please enter separator")
   If Sep <> "" Then
      Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp))), Sep)
   End If
End Sub

Hi Fluf,

Thank you for your swift response.

Please provide me the code for data in selection. The data is not specific for column, it could be in any column. Thank you.

 

[Fluff]

Maybe

Sub JoinData()

   Dim Sep As String
   
   Sep = InputBox("Please enter separator")
   If Sep <> "" Then
      Cells(Rows.Count, Selection.Column).End(xlUp).Offset(1).Value = Join(Application.Transpose(Range(Cells(1, Selection.Column), Cells(Rows.Count, Selection.Column).End(xlUp))), Sep)
   End If
End Sub

 

[Joe4]

Please provide me the code for data in selection. The data is not specific for column, it could be in any column. Thank you.

Did you consider my code?
It is used like any other Excel function, and can be applied to any range that you desire, and any delimiter that you want, and doesn't require you to manually run any VBA code.
If there is some feature of it that you would like changed, please let me know.

 

[Deepk]

Maybe

Sub JoinData()

   Dim Sep As String
   
   Sep = InputBox("Please enter separator")
   If Sep <> "" Then
      Cells(Rows.Count, Selection.Column).End(xlUp).Offset(1).Value = Join(Application.Transpose(Range(Cells(1, Selection.Column), Cells(Rows.Count, Selection.Column).End(xlUp))), Sep)
   End If
End Sub

Thank you. I am not sure why the final output is coming with five additional separators in the beginning. See the output below

|||||tata|bata|mata|tapa

 

[Fluff]

do you have 4 blank cells at the top of the column?

 

[Deepk]

Did you consider my code?
It is used like any other Excel function, and can be applied to any range that you desire, and any delimiter that you want, and doesn't require you to manually run any VBA code.
If there is some feature of it that you would like changed, please let me know.

Hi Joe4,

sorry for my late response. yes this code is working fine. thank you.

 

[Deepk]

do you have 4 blank cells at the top of the column?

yes but it is not in selection.