CODEWITHSUNDEEP
stringexcel
user2695707
user2695707

Reputation: 35

excel combine values in one row to a comma seperated string

i was looking around for a solution to this problem but i couldn't find a solution that satisfied me. So here's the thing: in excel i have one row with an unspecified amount of values (can be 30, can be 40, no one knows). What i want is to combine all those values in one cell separated by commas (a string). how can i do that with iterations? One more thing: if the field is empty, the loop has to end (at the end of the list of values)

heres some sample data:

ROW A:
----
a
b
c
d
e
f
g

Result: 

a,b,c,d,e,f,g

Thanks

Upvotes: 0

Views: 3069

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider the following User Defined Function (UDF):

Public Function conkittenate(rIn As Range) As String
    Dim r As Range
    conkittenate = ""
    For Each r In rIn
        If r.Value <> "" Then
            If conkittenate = "" Then
                conkittenate = r.Text
            Else
                conkittenate = conkittenate & ", " & r.Text
            End If
        End If
    Next
End Function

In the worksheet, it could be used like:

=conkittenate(A1:H1)

or

=conkittenate(A1:A5)

Upvotes: 2

Related Questions