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

J

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!
 
Sort by date Sort by votes

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Obviously I am late to this thread (unless amitray can use it), but here is a UDF solution that doesn't rely on a loop (although it is limited to a single row range or a single column range, but not a range having more than one row *and* column). The function takes two arguments... the first, the range you want to join, is required... the second, text you wish to use as a delimiter, is optional (if omitted, there will be no delimiter between the cell text)...

Code: 
Function JoinRange(varRange As Range, Optional varDelimiter As String) As String
  With WorksheetFunction
    If varRange.Columns.Count = 1 Then
      JoinRange = .Trim(Join(.Transpose(varRange.Value), varDelimiter))    [COLOR="#008000"]'Join down[/COLOR]
    Else
      JoinRange = .Trim(Join(.Index(varRange.Value, 1, 0), varDelimiter))  [COLOR="#008000"]'Join across[/COLOR]
    End If
    JoinRange = Replace(Replace(.Trim(Replace(Replace(JoinRange, " ", Chr(1)), _
                varDelimiter, " ")), " ", varDelimiter), Chr(1), " ")
  End With
End Function


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use JoinRange just like it was a built-in Excel function. For example,

=JoinRange(A1:P1,", ")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Thanks a lot. This seems to be an improved version. I was trying to join email ID's from a column with a semicolon as delimiter. This was really helpful.
 
Upvote 0
amitray said:
Thanks a lot. This seems to be an improved version. I was trying to join email ID's from a column with a semicolon as delimiter. This was really helpful.
Click to expand...

Hi Arris/ArmyTray

Before:

Excel 2010
Row\Col
A​
B​
C​
1​
Tom1Dick1Arries1
2​
Tom2Dick2Arries2
3​
Tom3Dick3Arries3
4​
Tom4Dick4Arries4
5​
Tom5Dick5Arries5
6​
Tom6Dick6Arries6
7​
Tom7Dick7Arries7
Aris

After:

Excel 2010
Row\Col
A​
B​
C​
D​
1​
Tom1Dick1Arries1Tom1 ; Dick1 ; Arries1
2​
Tom2Dick2Arries2Tom2 ; Dick2 ; Arries2
3​
Tom3Dick3Arries3Tom3 ; Dick3 ; Arries3
4​
Tom4Dick4Arries4Tom4 ; Dick4 ; Arries4
5​
Tom5Dick5Arries5Tom5 ; Dick5 ; Arries5
6​
Tom6Dick6Arries6Tom6 ; Dick6 ; Arries6
7​
Tom7Dick7Arries7Tom7 ; Dick7 ; Arries7
Aris

Code:

Code: 
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] arisConcatenating()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]

[color=lightgreen]'Input Ranges[/color]
[color=blue]Dim[/color] rngA [color=blue]As[/color] Range: [color=blue]Set[/color] rngA = wks.Range("A1:A" & lr & "")
[color=blue]Dim[/color] rngB [color=blue]As[/color] Range: [color=blue]Set[/color] rngB = wks.Range("B1:B" & lr & "")
[color=blue]Dim[/color] rngC [color=blue]As[/color] Range: [color=blue]Set[/color] rngC = wks.Range("C1:C" & lr & "")

[color=lightgreen]'Conctente with Evaluate[/color]
[color=blue]Let[/color] rngD.Value = Evaluate("" & rngA.Address & "" & "&"" ; ""&" & " " & rngB.Address & "" & "&"" ; ""&" & " " & rngC.Address & "")

[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating[/color]


Full Explanation Here:
http://www.mrexcel.com/forum/excel-...nto-single-column-using-data-text-column.html
And Here
Multiple Columns Into Single Column Using Data Text To Column - Page 2

Need any more help, how to get stated with Macros etc., then get back..

Alan
 
Upvote 0
DocAElstein said:
Code: 
[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
[COLOR=blue]Sub[/COLOR] arisConcatenating()
[COLOR=blue]Dim[/COLOR] wks [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wks = ThisWorkbook.Worksheets("Aris") [COLOR=lightgreen]'Sheet Info[/COLOR]
[COLOR=blue]Dim[/COLOR] lr [COLOR=blue]As[/COLOR] Long: [COLOR=blue]Let[/COLOR] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[COLOR=blue]Dim[/COLOR] rngD [COLOR=blue]As[/COLOR] Range: [COLOR=blue]Set[/COLOR] rngD = wks.Range("D1:D" & lr & "") [COLOR=lightgreen]'Output Range[/COLOR]

[COLOR=lightgreen]'Input Ranges[/COLOR]
[COLOR=blue]Dim[/COLOR] rngA [COLOR=blue]As[/COLOR] Range: [COLOR=blue]Set[/COLOR] rngA = wks.Range("A1:A" & lr & "")
[COLOR=blue]Dim[/COLOR] rngB [COLOR=blue]As[/COLOR] Range: [COLOR=blue]Set[/COLOR] rngB = wks.Range("B1:B" & lr & "")
[COLOR=blue]Dim[/COLOR] rngC [COLOR=blue]As[/COLOR] Range: [COLOR=blue]Set[/COLOR] rngC = wks.Range("C1:C" & lr & "")

[COLOR=lightgreen]'Conctente with Evaluate[/COLOR]
[COLOR=blue]Let[/COLOR] rngD.Value = Evaluate("" & rngA.Address & "" & "&"" ; ""&" & " " & rngB.Address & "" & "&"" ; ""&" & " " & rngC.Address & "")

[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR] [COLOR=lightgreen]'arisConcatenating[/COLOR]
Click to expand...
For the way you structured your code, I would not bother creating ranges rngA, rngB or rngC solely to be able to pull out their addresses... just make the variables String data types and assign the address directly. Doing it that way should be more efficient...

Code: 
Sub arisConcatenating1()
  Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Aris") 'Sheet Info
  Dim lr As Long: Let lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
  Dim rngD As Range: Set rngD = wks.Range("D1:D" & lr & "") 'Output Range
  'Input Ranges
  Dim rngA As String: rngA = "A1:A" & lr
  Dim rngB As String: rngB = "B1:B" & lr
  Dim rngC As String: rngC = "C1:C" & lr
  'Concatente with Evaluate
  rngD.Value = Evaluate(rngA & "&"" ; ""&" & rngB & "&"" ; ""&" & rngC)
End Sub 'arisConcatenating

However, you can shorten things up by eliminating those range variables and embedding the addresses directing into the String argument for the Evaluate function...

Code: 
Sub arisConcatenating2()
  Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Aris") 'Sheet Info
  Dim lr As Long: Let lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
  Dim rngD As Range: Set rngD = wks.Range("D1:D" & lr & "") 'Output Range
  
  'Concatente with Evaluate
  rngD.Value = Evaluate(Replace(Replace("@A1:A#&"" ; ""[EMAIL="&@B1:B"]&@B1:B[/EMAIL]#&"" ; ""[EMAIL="&@C1:C"]&@C1:C[/EMAIL]#", "@", "'" & wks.Name & "'!"), "#", lr))
  
End Sub 'arisConcatenating

Note that I use stand in characters for the worksheet name and last row and then replace them with their actual values using two Replace function calls... this eliminates a horrendous number of concatenations and makes constructing the String argument for the Evaluate function much easier.
 
Last edited:
Upvote 0
. Amazing Rick. I (think**) I am glad I popped into this Thread

. I follow he logic with code 1

Code: 
[color=blue]Sub[/color] arisConcatenating1a()
  [color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
  [color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
  [color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]
  'Input Ranges
  [color=blue]Dim[/color] rngA [color=blue]As[/color] String: rngA = "A1:A" & lr
  [color=blue]Dim[/color] rngB [color=blue]As[/color] String: rngB = "B1:B" & lr
  [color=blue]Dim[/color] rngC [color=blue]As[/color] String: rngC = "C1:C" & lr
  [color=lightgreen]'Concatente with Evaluate[/color]
  rngD.Value = Evaluate(rngA & "&"" ; ""&" & rngB & "&"" ; ""&" & rngC)
  rngD.Value = Evaluate(rngA & "" & "&"" ; ""&" & " " & rngB & "" & "&"" ; ""&" & " " & rngC)
  
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating[/color]

. I am almost there with code 2.

Code: 
[color=blue]Sub[/color] arisConcatenating2a()
  [color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
  [color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
  [color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]
  rngD.Clear 'Just so I know the next lines work!!
  
  [color=lightgreen]'Concatente with Evaluate[/color]
  rngD.Value = Evaluate(Replace(Replace("@A1:A#&"" ; ""&@B1:B#&"" ; ""&@C1:C#", "@", "'" & wks.Name & "'!"), "#", lr))
  rngD.Value = Evaluate(Replace(Replace("@A1:A#&"" ; ""&@B1:B#&"" ; ""&@C1:C#", "@", "'" & wks.Name & "'!"), "#", "" & lr & ""))
  
  rngD.Value = Evaluate(Replace("'" & wks.Name & "'!A1:A#&"" ; ""&'" & wks.Name & "'!B1:B#&"" ; ""&'" & wks.Name & "'!C1:C#", "#", lr))

  [color=lightgreen]'rngD.Value = Evaluate("'" & wks.Name & "'!A1:A"" & lr & ""&"" ; ""&'" & wks.Name & "'!B1:B"" & lr & ""&"" ; ""&'" & wks.Name & "'!C1:C"" & lr & """)[/color]
  [color=lightgreen]'rngD.Value = Evaluate("'" & wks.Name & "'!A1:Alr&"" ; ""&'" & wks.Name & "'!B1:Blr&"" ; ""&'" & wks.Name & "'!C1:Clr")[/color]

[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating[/color]


[color=lightgreen]'       Replace(Replace("@A1:A#&"" ; ""&@B1:B#&"" ; ""&@C1:C#", "@", "'" & wks.Name & "'!"), "#", lr)[/color]

[color=lightgreen]'               Replace("@A1:A#&"" ; ""&@B1:B#&"" ; ""&@C1:C#", "@", "'" & wks.Name & "'!")[/color]
[color=lightgreen]'       =       '" & wks.Name & "'!A1:A#&"" ; ""&'" & wks.Name & "'!B1:B#&"" ; ""&'" & wks.Name & "'!C1:C#[/color]
[color=lightgreen]'[/color]
'   Replace("'" & wks.Name & "'!A1:A#&"" ; ""&'" & wks.Name & "'!B1:B#&"" ; ""&'" & wks.Name & "'!C1:C#", "#", lr)
[color=lightgreen]'   =  '" & wks.Name & "'!A1:Alr&"" ; ""&'" & wks.Name & "'!B1:Blr&"" ; ""&'" & wks.Name & "'!C1:Clr[/color]
[color=lightgreen]'[/color]
'   Replace("'" & wks.Name & "'!A1:A#&"" ; ""&'" & wks.Name & "'!B1:B#&"" ; ""&'" & wks.Name & "'!C1:C#", "#", "" & lr & "")
[color=lightgreen]'   =  '" & wks.Name & "'!A1:A"" & lr & ""&"" ; ""&'" & wks.Name & "'!B1:B"" & lr & ""&"" ; ""&'" & wks.Name & "'!C1:C"" & lr & ""[/color]
[color=lightgreen]'[/color]
'

. can you help me with where I am falling down on my last two 'rngD.Value = attempts?
. It is driving me crazy!**

Thanks
Alan
 
Upvote 0
DocAElstein said:
. can you help me with where I am falling down on my last two 'rngD.Value = attempts?
. It is driving me crazy!**
Click to expand...
"Falling down" how? Can you orient me as to the problem you (think you) are having so I know where to look?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Pivot Table Sorting
Replies
1
Views
576
RoryA
RoryA
J
  • Question
Power Query - Column that Concatenates a column and a text version of a date.
Replies
2
Views
145
jaihawk8
J
K
  • Solved
VBA Copy certain cells and columns to another sheet
2
Replies
16
Views
416
karl10220
K
S
  • Question
VBA Copy range of data from multiple sheets & paste into single column of summary sheet
Replies
1
Views
482
Micron
M
F
  • Question
VBA to take a field value from 1 worksheet and update that field value to another worksheet based on another cell value
Replies
4
Views
179
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,632
Messages
6,125,909
Members
449,274
Latest member
mrcsbenson

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