Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Brooklyn Park, Minnesota, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate many values into string (97 SR2)

    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
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •