Copy data to one cell

Microsoft Office excel 2007 home & stude...
August 5, 2010 at 17:48:00
Specs: Windows XP
How do i copy the data from rows in one cell?
For example,if I have 100 line items and i need to enter in Access as query. I want to put it in one cell and insert 'OR' so that i can paste in Access.


See More: Copy data to one cell

Report •

August 5, 2010 at 18:12:57
Typically, the CONCATENATE function or Concatenation Operator & is used to join cells and text strings:

=CONCATENATE(A1, "OR", A2, "OR" ,A3)

=A1 & "OR" & A2 &"OR" & A3

However, this would really be a pain to enter for 100 items.

Instead, I've written a User Defined Function (UDF) to perform the Concatenation for you.

1 - Open the VBA Editor via Alt-F11
2 - In a Standard Module, paste the code found below
3 - In any worksheet cell enter:


(Replace A1:C5 with the range of cells you want to concatenate)

Option Explicit
Function myQuery(target As Range)
Dim myElement As Integer
Dim buildQuery As String
'Loop through range
  For myElement = 1 To target.Cells.Count
'Concatenate cells, adding " OR " after each one
   buildQuery = buildQuery & target.Cells(myElement) & " OR "
'Strip off last " OR " and return string
   myQuery = Left(buildQuery, Len(buildQuery) - 4)
End Function

Report •

August 5, 2010 at 18:42:41
Thank you soo much. It works great :)

You made my day!You are a genius

Thanks a lot.

Report •

August 5, 2010 at 18:47:09
I'm hardly a genius...but thanks anyway.

I'm glad it worked for you.

Report •

Related Solutions

Ask Question