How to concatenate many rows in Excel
 

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

How to concatenate many rows in Excel
 

One way:

Copy your formula in D1.

Select D1:D1000 (or wherever your last row is)

Paste.

XL will adjust the row numbers for you.

In article , Christina Haney
wrote:

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

How to concatenate many rows in Excel
 

Christina Haney wrote:
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.

How to concatenate many rows in Excel
 

Holy crap! It worked! That's so awesome, I could throw up. You just save me DAYS of work! Thanks so much!

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