How to concatenate many rows in Excel

[Christina Haney]

I'm kind of a newbie so hopefully I can explain this properly. I have a worksheet with three columns of text. I've created a fourth column into which I want to combine the other three columns. I'm using the following formula in the corresponding row of column D
=CONCATENATE(A1," ",B1," ",C1) goes into D1
=CONCATENATE(A2," ",B2," ",C2) goes into D2
=CONCATENATE(A3," ",B3," ",C3) goes into D3
and so on.
The problem is, I need to repeat this formula over 1,000 times and I'd like to know if there's another way to do it because it's getting kind of tedious. Right now I have to manually change the row number for each concatenate formula which sucks! Is there a better way to do it?

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[JE McGimpsey]

One way:

Copy your formula in D1.

Select D11000 (or wherever your last row is)

Paste.

XL will adjust the row numbers for you.

 

[Glenn]

I'm kind of a newbie so hopefully I can explain this properly. I have a worksheet with three columns of text. I've created a fourth column into which I want to combine the other three columns. I'm using the following formula in the corresponding row of column D
=CONCATENATE(A1," ",B1," ",C1) goes into D1
=CONCATENATE(A2," ",B2," ",C2) goes into D2
=CONCATENATE(A3," ",B3," ",C3) goes into D3
and so on.
The problem is, I need to repeat this formula over 1,000 times and I'd like to know if there's another way to do it because it's getting kind of tedious. Right now I have to manually change the row number for each concatenate formula which sucks! Is there a better way to do it?

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

=A1&" "&B1&" "&C1

will give the same result. Then look at "Move or copy a formula" in the help file.