formula or functions needed

[ericlch16]

I have column A which has

1234
123461
123151
11321
(note that the rows are dynamic. it is 4 but can be more)


I need to change the values in column A and make them into a comma-separated list in another cell that the user can easily copy and paste into another program. It should be like this:-

1234,123461,123151,11321

What is the quickest way to do it ? Formulas ? I tried paste special transpose but cannot achieve it ? I can do it in macro but wondering if there are other simple ways to do it.

Any help?

 

[oldbrewer]

a helper cell with =a1&a2&a3......up to say a20

if a5 = blank it will not "show" - - do you need the commas ?

 

[sheetspread]

You can type =a1:a4 (or whatever your range is) in another cell, highlight, F9, and copy/paste the resulting array of constants from the formula bar. Ctrl-h can change the delimiters if you wish. Or type =a1&a2 in a5 and drag down for an incremental concat then copy/paste as values the desired cell.

 

[Nishant94]

If you have Excel 2016, then you can use

=TEXTJOIN(",",,A1:A4)

If the last row is not known then you can use this(For this also you need Excel 2016):

 =TEXTJOIN(",",,A:A)

 

[Learn Excel in Tamil]

Hi

Please try with below coding

Sub Test()


Dim i As String
Dim Lrow As String
Dim Result As String


Lrow = Range("A100000").End(xlUp).Row


Range("A1:A" & Lrow).Select


For Each Cell In Selection
    i = Cell.Value
    If Result = "" Then
    Result = i
    Else
    Result = Result & "," & i
    End If
Next


Range("B1").Value = "'" & Result


End Sub