Copying concatenate formula to multiple cells

**AndianAtWork** · 06-18-2012, 07:33 PM

It may be user error, but I'm not 100% sure. I am trying to copy a Concatenate formula to about 800 cells. Column one needs to be merged with column two. I have =CONCATENATE(A1,B1). I copy that to highlighted cells but it will only go into the first cell, but use the information in A1 and B1 over again, which isn't what I want. Say I have a large selection of these items that need to be merged (I'm not doing them all at once, but in chunks), what would be a good concatenate formula for these? I would need A and B down to whatever row (let's just say 200). Attached is a sample sheet.

remussampler.xls

Any help and advice is greatly appreciated. Thank you!

**Leith Ross** · 06-18-2012, 07:45 PM

Hello AndianAtWork,

Excel's CONCATENATE formula is not very useful. You can replace it by use the ampersand character &. For example, =A1&B1. Either way you very limited because neither can be used to create Formula Arrays. Do you want the HTML tags left in the text? A VBA macro would offer you more flexibity.

**AndianAtWork** · 06-18-2012, 08:03 PM

Yes, I'd like the HTML tags left in. I'm not all that great with macros. What would you suggest I try?

Thank you for the quick reply!

**Leith Ross** · 06-18-2012, 09:22 PM

Hello AndianAtWork,

If you only need A1&B1, A2&B2, etc. down to the last entry then you could simply drag the formula you have in cell C1 down the column.