How to concatenate many rows in Excel

  • Thread starter Thread starter Christina Haney
  • Start date Start date
C

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
 
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.
 
Christina said:
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
Click to expand...


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

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