Concatenate many values into string (97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

Use:

=”‘”&MCONCAT(A1:A50,”‘,'”)&”‘”

MCONCAT is a function (among many others) available in Longre’s Morefunc add-in, which is downloadable from:

http://longre.free.fr/english/index.html%5B/url%5D

Reply | Quote