convert excel column data to comma seperated string

I have excel file in which data is present in a column as


234
654
222
444
667
555


I want this text in this form 234,654,222,444,667,555.I tried many ways bt unable to do so,Kindly tell me any easy way of doing this.

Formula wise it's a little convoluted unless you make use of 3rd party utilities like morefunc.xll and / or other custom UDFs of which there are plenty if you Google.

A basic example of such a UDF being:

Function CONCATSTR(rngVals As Range, strDelim As String, Optional boolCap As Boolean)
Dim vData, lngCols As Long, lngCol As Long, strTemp As String
lngCols = rngVals.Columns.Count
If Application.CountA(rngVals) = 0 Then CONCATSTR = "Empty": Exit Function
If rngVals.Rows.Count > 1 Then
    If lngCols = 1 Then
        With rngVals
            vData = Application.Transpose(.Parent.Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))"))
        End With
    Else
        For lngCol = 1 To lngCols Step 1
            If Application.CountA(rngVals.Columns(lngCol)) Then
                With rngVals.Columns(lngCol)
                    vData = Application.Transpose(.Parent.Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))"))
                End With
                If lngCol < lngCols Then
                    CONCATSTR = CONCATSTR & Replace(Replace(Application.WorksheetFunction.Trim(Join(vData, " ")) & " ", " ", strDelim), "^", " ")
                End If
            End If
        Next lngCol
    End If
Else
    With rngVals
        vData = .Parent.Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))")
    End With
End If
CONCATSTR = CONCATSTR & Replace(Replace(Application.WorksheetFunction.Trim(Join(vData, " ")), " ", strDelim), "^", " ")
If boolCap Then CONCATSTR = strDelim & CONCATSTR & strDelim
End Function

With the above stored in a Module in VBE you can utilise from native XL by virtue of a "normal" function in which you simply specify the range containing the values to be concatenated and preferred delimiter, eg:

=CONCATSTR(A1:B100,",")

would concatenate all non-blanks in the range, each value being delimited by a comma.

Hi,

Another approach would be:

Function CONCAT(R As Range, Delim As String) As String
Dim Rng As Range
Dim st As String
If Application.CountA(R) = 0 Then CONCAT = "Empty": Exit Function
For Each Rng In R
    If Not (IsEmpty(Rng)) Then st = st & Delim & Rng.Value
Next Rng
CONCAT = Right(st, Len(st) - 1)
End Function

Orlando Mezquita

I should elaborate perhaps in response to above variant the reasons for the apparent complexity of the first code.

The first code is designed to limit the need for iteration as much as possible and/or dispense with it fully where possible.

The code will iterate only where range is a matrix (as opposed to a vector) and then only on a column by column basis - ie cell by cell iteration is avoided.