Alternative to Transpose and Concatenate

[jamieleeuk]

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

 

[onlyadrafter]

Hello,

can do it via VB.

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!]

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]

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

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

 

[jamieleeuk]

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!]

nopez.. there isn't...

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

 

[jamieleeuk]

excellent - thanks all