Combine data from a column to a single cell

D

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
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
tata
bata
mata
pata
gata

<tbody>
</tbody>
macro code generate an inputbox
inputbox,

<tbody>
</tbody>

Output-->>

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

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
How about
Code: 
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
 
Upvote 0
Here is a Function that will do that for you. Simply provide the range and the separator, and it will do what you want.
Code: 
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,"|")
 
Upvote 0
Fluff said:
How about
Code: 
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
Click to expand...

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.
 
Upvote 0
Maybe
Code: 
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
 
Upvote 0
Please provide me the code for data in selection. The data is not specific for column, it could be in any column. Thank you.
Click to expand...
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.
 
Upvote 0
Fluff said:
Maybe
Code: 
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
Click to expand...

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
 
Last edited:
Upvote 0
do you have 4 blank cells at the top of the column?
 
Upvote 0
Joe4 said:
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.
Click to expand...

Hi Joe4,

sorry for my late response. yes this code is working fine. thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Duplicates with different data to combine with VBA
Replies
2
Views
99
chipsworld
C
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
303
Fluff
Fluff
S
  • Question
VBA Copy range of data from multiple sheets & paste into single column of summary sheet
Replies
1
Views
454
Micron
M
E
  • Solved
Delete row based on whether cell in alternate column in row has data
Replies
3
Views
81
LazyBug
LazyBug
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
746
Plod518
P

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top