Concatenate formula

**Hblbs** · 10-14-2009, 11:03 AM

Hi all,

I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.

Is this possible? I've tried to use the formula below with no luck,

Please Login or Register  to view this content.

Any help is appreciated.

**firefly2k8** · 10-14-2009, 12:00 PM

you could try the & symbol instead

=A1&B1& etc

or type =concatenate( then hold down the control key whilst selecting each cell you want (you cannot just drag a range)

**NBVC** · 10-14-2009, 12:29 PM

Install this UDF in your VB editor:

Please Login or Register  to view this content.

and then use formula:

=aconcat(A1:GR1)

Note, you can also incorporate separtors with this function

e.g

=aconcat(A1:GR1,",") will put commas between each item...

**Hblbs** · 10-14-2009, 01:24 PM

firefly2k8, thanks for the reply. I have tried this but have since found that concatenate formula on goes up to 30, where as I need 200.

NBVC, thank you for the reply I thinks this is what I need. Can I just ask how this is installed? I've gone into VB and pasted this but it does not seem to work.

**NBVC** · 10-14-2009, 01:45 PM

When you go to the VB editor (Alt+F11)

ensure that you are in your current workbook.. within the project explorer window on the left. Once you are in your workbook, go to Insert|Module and then paste it in the editor window.