Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 312

# concatenate 2 columns down 100 rows

data looks like this
Col1    Col2
2345 Sore throat
etc down 100 rows
Would like result to look like this (in one cell)

Schuyler
0
schuyler
• 4
• 3
• 2
• +3
1 Solution

Commented:
schuyler,

You would need VBA code for that.

For example:

``````Function Concat(Col1 As Range, Col2 As Range, Optional Delim1 As String = "-", Optional Delim2 As String = "; ")

Dim Counter As Range

For Counter = 1 To Col1.Cells.Count
Concat = Concat & Delim2 & Col1.Cells(Counter) & Delim1 & Col2.Cells(Counter)
Next

Concat = Mid(Concat, Len(Delim2) + 1)

End Function
``````

Assuming your data are in, say, A2:B100...

=Concat(A2:A100,B2:B100)

Patrick
0

Commented:
Hi Schuyler,

You could try the following code as well.

=A2& "-" &B2
Combine.JPG
0

Commented:
assume data in A1 & B1 in C1 put =A1 & "-" & B1
use enter to finish editing.
move the mouse to the bottom right hand corner of the cell the cursor will change to a narrow cross.
Either drag down to row 100, or if you have data that stops at row 100 just double click, the formula will autofill down until it comes to a break in the data.
You could of course use both techniques, use the double click then goto the bottom cell (by double clicking anywhere else on the bottom edge) and then just drag down the last few rows :)
0

Author Commented:
Thanks...but I know how to do that, what I need to do is have all the data in one cell, I need to transfer it to another sheet as a list of data

Patrick,
in the excel file, in a cell I put =Concat(A19:B176,B2:C176) but nothing is happening (yes I put the function in a module)
Thanks
0

Commented:
schuyler,

Patrick
0

Commented:
Schuyler, you could also look at this previous question and see if the same solution works for you,
regards
0

Commented:
Here's an example of the code. I've attached a sample sheet for you to test.

jppinto
``````Sub ConcatenateText()

Dim lastRow As Long
Dim i As Long
Dim p As Long
Dim conValue As String

lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
conValue = conValue & Sheets("Sheet1").Cells(i, 1).Value & "-" & Sheets("Sheet1").Cells(i, 2).Value & ";"
Next
Sheets("Sheet1").Cells(1, 6).Value = conValue

End Sub
``````
ConcatenateText-VBA.xlsm
0

Author Commented:
Patrick,
I can't send the file but if you look at what example that royhsiao posted above, that's what the file looks like, even column C...if we could use that column using an ";" btwn each set of values that would work as well.

thanks
0

Commented:
Did you tryed my code?
0

Author Commented:
jppinto
Where do I put the call?  I can step thru your code jppinto but where is it on the spreadsheet...sorry its been a while since I needed to code in excel....appreciate everyone's help
0

Commented:
Alt+F11 takes you to the VBA editor. You can place your code into your file and the click on the Run button (or press F5) to run your code on your sheet.

jppinto
Capture.JPG
0

Author Commented:
thanks so much!

patrick I know your code was similar but i couldn't get it to work and jppinto's did...appreciate your efforts!!

Schuyler
0

## Featured Post

• 4
• 3
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.