Help Concatenate All Cells In A Column To A Single Cell In Another Column

I have three columns. Column A has a list of file names. Column B has each cell filled with the word OR. Column C Concatenates Columns A and B together to give me something like this:

Document01 OR
Document02 OR
Document03 OR
Document04 OR

What I'd like to do with this is concatenate all of the cells in Column C into the first cell of Column D, to give me something like this:

Document01 OR Document 02 OR Document03 OR Document04

I can then copy and paste that cell into a search box in windows (or another file finding program) to find all of the files I originally pasted in Column A.

I have everything working except that I have to edit the formula in the first cell of Column D by hand, adding every cell I'd like to concatenate to it like this:

=CONCATENATE(C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C258,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,C73,C74,C75,C76,C77,C78,C79,C80,C81,C82,C83,C84,C85,C86,C87,C88,C89,C90,C91,C92,C93,C94,C95,C96,C97,C98,C99,C100)

Is there an easier way to do this? Note that I won't always know exactly how many cells I'll have filled in Column A. If I could make it concatenate infinately that would be great. Or at least up to 2000 if that's possible. Sorry if this is a stupid question, I'm new to excel and formulas.

I've attached a copy of what I have so far.

Thanks!

Originally Posted by Rahul Nagar

1st copy your column C and
2nd paste special Transpose

Thanks for the help, but that doesn't appear to be working. When I copy Column C and paste it I just get a bunch of this: #REF!.
I assume that when I do the copy, it's trying to copy the formula that's in the cell, not the results of the formula. I'm sure there's a way to copy it properly but I'm unable to do it. I'll keep trying different things.

Thanks! I appreciate the help! I was hoping to do this with less copying and pasting however. It is a solution for now at least. Thanks!

Here, try this UDF:

First one use C column
Second one use A column

Perhaps

In D2: =C2
In D3: =D2&C3 and dragged down

Assuming you have contiguous data in column C, put this in D2:

=C2

and this in D3:

=D2 & C3

then copy this down to the bottom of your data - hint, select D3 then double-click the fill handle (the small black square in the bottom right corner of the cursor.

The bottom cell will now contain all those concatenated values, so you could get them into cell E2, for example, using this:

=INDIRECT("D"&COUNTA(D:D))

Hope this helps.

Pete