concatenate 2 columns down 100 rows

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

Select allOpen in new window

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

=Concat(A2:A100,B2:B100)

Patrick

Hi Schuyler,

You could try the following code as well.

=A2& "-" &B2
Combine.JPG

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 :)

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

schuyler,

Please post your file.

Patrick

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

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

Did you tryed my code?

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

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

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