Solved

# concatenate 2 columns down 100 rows

Posted on 2011-05-02
272 Views
data looks like this
Col1    Col2
2345 Sore throat
etc down 100 rows
Would like result to look like this (in one cell)
2345-Sore throat; 1123Q-Bad Cough; etc

Thanks in advance for your prompt response!

Schuyler
0
Question by:schuyler

LVL 92

Expert Comment

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

LVL 6

Expert Comment

Hi Schuyler,

You could try the following code as well.

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

LVL 4

Expert Comment

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 Comment

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

LVL 92

Expert Comment

schuyler,

Patrick
0

LVL 7

Expert Comment

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

LVL 33

Accepted Solution

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 Comment

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

LVL 33

Expert Comment

Did you tryed my code?
0

Author Comment

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

LVL 33

Expert Comment

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 Closing Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

#### 761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!