# Alternative to Transpose and Concatenate

#### jamieleeuk

##### Board Regular
I have data in range A6:A60, I need to copy this data into another program which requires it on one row and obviously with there being 60 rows it only pastes the first. The data also has to be split with a comma.

What I have done in the past is :

=concatenate(A1,",",A2,",",A3... A15)

as concatenate only allows me to concatenate 15 cells, I have to perform 4 or 5 different concatenate formulas and then concatenate the results.

I want to know if there's an easier way of mashing them all up together in one cell seperated by a comma other than the way I am currently going about it?

Thanks

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### Well-known Member
Hello,

can do it via VB.

Code:
``````Sub CONCAT()
For MY_ROWS = 6 To 60
MY_WORD = MY_WORD & Range("A" & MY_ROWS) & ","
Next MY_ROWS
Range("B6").Value = Left(MY_WORD, Len(MY_WORD) - 1)
End Sub``````

#### Willem!

##### Board Regular
Hi!

Use a help-column.

Place in (e.g.) B2, the following formula:
=CONCATENATE(A1,",",A2,",")

Place in B3 the following formula:
=CONCATENATE(B2,A3,",")

Auto-fill to row 60, remove the last comma, and you'll have your data

HTH!

#### Stormseed

##### Banned
Hi, this worked fine for me and it would let me do a concatanate for more than 15 cells in one go

PHP:
``=A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","&O1&","&P1&","&Q1&","&R1&","&S1&","&T1``

#### jamieleeuk

##### Board Regular
Seriously dude, that's legendary! Thanks.

Whilst I will quite happily use that, is there an actual xl formula which will perform the same task?

Ta

#### Willem!

##### Board Regular
nopez.. there isn't...

onlyadrafter's suggestion to make a UDF is the closest you can get

#### jamieleeuk

##### Board Regular
excellent - thanks all

Replies
0
Views
19
Replies
9
Views
380
Replies
4
Views
225
Replies
2
Views
69
Replies
5
Views
318

1,191,025
Messages
5,984,198
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back