Combining numbers from several cells and a comma after each

[dmb41]

I am looking to take several cells,
i.e.,

1 4 5 6 7
1 3 5 2 6
1 3 4 5 6
2 4 5 6 7

and combine into one cell with comma after each number.

1,4,5,6,7,1,3,5,........7,

I have been using the =concatenate() but it gets very tedious for many cells

Any shortcuts?

 

[Marcelo Branco]

Hi,

Copy the function below to a standard module
Alt+F11, Insert, Module and paste it in the right-panel

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Enter this in any cell
=Aconcat(A1:E4,",")

Adjust the range accordingly

HTH

M.

 

[MickG]

Try this UDF:-
Right click sheet Tab, Select "View Code" vb window appears.
From the vb Window Toolbar Select "Insert, "Module".
New VB Window appears.
Paste Code into Vb Window.
Close VB Window.

To run code, Enter in cell :- = Com(Select you range of cells Here)
and select range of cells , as shown between Brackets.

Function Com(rng As Range) As String
Dim Rw As Range
Dim txt As String
For Each Rw In rng
    txt = txt & Rw & ", "
Next Rw
Com = Left(txt, Len(txt) - 2)
End Function

Mick