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

jason6084

New Member
Joined
Aug 13, 2009
Messages
7
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
Is this a one-time operation or might you need to do this again?

Is a VBA solution acceptable?
 
Upvote 0
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.
 
Upvote 0
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.

Code:
<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>
 
Upvote 0
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:-
Code:
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
 
Upvote 0
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-com:office:office" /><o:p></o:p>
<o:p> </o:p>Thank you so so so much! Very clever! J
 
Upvote 0
I would use code
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
 
Upvote 0
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.
 
Upvote 0
If you want a VBA solution then I'd use a function rather than a subroutine - supplying the range to concatenate as an argument :-

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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