How do I Concatenate Multiple Rows or columns. (In my case 100)

[jason6084]

I have several rows of data that contains data in the following format.
123,
abc,
456,
def,
789,

What I would like to do is Concatenate them all together to get the result
123,abc,456,def,789,

I have a total of 100 rows like this that I am trying to concatenate. I have tried all sorts of things but it seems most things I have found are only good for concatenating a couple of items.

If needed I can paste transpose the rows into columns if it is easier to Concatenate multiple columns.

Either way my goal is string the items together.

Any help is greatly appreciated!

 

[Kaps_mr2]

lets assume that your data is in column G starting at cell G7. In cell I7 put '=G7' - no quotes. In Column I8 put '=I7&G8' and then copy down. Thanks

Kaps

 

[Ruddles]

Is this a one-time operation or might you need to do this again?

Is a VBA solution acceptable?

 

[jason6084]

lets assume that your data is in column G starting at cell G7. In cell I7 put '=G7' - no quotes. In Column I8 put '=I7&G8' and then copy down. Thanks

Kaps


Hi Kaps thanks for the response.. I have tried that. While the formula works it doesnt actually concatenate all the rows. When I go to paste special and paste the actual value all I get is the first vaule.

I am needing something that ties all of them together so that when I go to do paste vaules I get all the of the values in one cell.

 

[MrKowz]

Jason,

What Kaps suggested will concatenate all the rows. It uses a recurring formula that takes the result of one concatenate and then concatenates the next value to it.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>
123, 
 
 
 
 

</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64 x:fmla="=A1">123,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>abc,</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=C1&A2">123,abc,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>456,</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=C2&A3">123,abc,456,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>def,</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=C3&A4">123,abc,456,def,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>789, 
 
 
 

</TD><TD class=xl24 id=td_post_2439317 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=C4&A5">123,abc,456,def,789,</TD></TR></TBODY></TABLE>

 

[Ruddles]

Press Alt-F11 and paste this into the code window for your worksheet, then either run it from there (F5) or return to your worksheet and go Developer > Macros, then select ChChChain:-

Option Explicit
 
Public Sub ChChChain()
 
  Dim rSource As Range
  Dim rTarget As Range
  Dim oCell As Range
  Dim sConcat As String
  
  On Error GoTo woops
  Set rSource = Application.InputBox("Select cells to concatenate:-", Type:=8)
  Set rTarget = Application.InputBox("Select target location:-", Type:=8)
  
  For Each oCell In rSource
    sConcat = sConcat & CStr(oCell.Value)
  Next oCell
  
  rTarget.Value = sConcat
 
woops:

End Sub

 

[jason6084]

Many apologies Kap! This is quite the clever piece of work you have here. Very sweet! Sorry I misread it. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>Thank you so so so much! Very clever! J

 

[lenze]

I would use code

Sub JoinMe()
Dim cl as Range
Dim myString as String
For Each cl in Selection
myString = myString & cl
Next cl
MsgBox myString
Range("G1") = myString
End Sub

lenze

 

[Kaps_mr2]

You need to paste special the LAST value not the first one. thanks

Kaps

lets assume that your data is in column G starting at cell G7. In cell I7 put '=G7' - no quotes. In Column I8 put '=I7&G8' and then copy down. Thanks

Kaps


Hi Kaps thanks for the response.. I have tried that. While the formula works it doesnt actually concatenate all the rows. When I go to paste special and paste the actual value all I get is the first vaule.

I am needing something that ties all of them together so that when I go to do paste vaules I get all the of the values in one cell.

 

[Kaps_mr2]

If you want a VBA solution then I'd use a function rather than a subroutine - supplying the range to concatenate as an argument :-

Public Function multi_string(data_range As Range)
Dim no_of_rows As Long
Dim r As Long

no_of_rows = data_range.Rows.Count
For r = 1 To no_of_rows
    multi_string = multi_string & data_range(r, 1)
Next r

End Function

thanks

Kaps