# Thread: Concatenate many values into string (97 SR2)

1. ## Concatenate many values into string (97 SR2)

I have a list of 50+ values in a column. I want to concatenate them all into a string, with single quote around each value and commas separating them. For example, I want this list to become the string below it.

100390
140588
141621

'100390', '140588', '141621'

I've looked at the help for the concatenate function and it looks like I would have to select each cell individually to get text between them. With over 50 values, this would take as long as typing them all in! How can I do this quickly?

2. ## Re: Concatenate many values into string (97 SR2)

You can write a user-defined function

Function ConcatenateRange(aRange As Range) As String
Dim aCell As Range
Dim strReturn As String
For Each aCell In aRange
strReturn = strReturn & ", '" & aCell.Value & "'"
Next aCell
If strReturn <> "" Then
strReturn = Mid\$(strReturn, 3)
End If
ConcatenateRange = strReturn
End Function

and then use the following formula in a cell:

=ConcatenateRange(A1:A55)

3. ## Re: Concatenate many values into string (97 SR2)

A non-macro solution:

Assuming your list starts on A2.

on B2 enter:

="'" & A2 & "'"

on B3 enter:

=B2 & ",'" & A3 & "'"

Copy B3 down to fit the number of columns.

4. ## Re: Concatenate many values into string (97 SR2)

Use:

="'"&MCONCAT(A1:A50,"','")&"'"