Consulting

Results 1 to 5 of 5

Thread: Solved: combining lists into a string

  1. #1
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    273
    Location

    Solved: combining lists into a string

    i was wondering if there is a way to take a range of cells and turn them into a string with the same string between each cell value. i realize this is a rather easy thing to do in code, but is there a way to do it live on the sheet?

    example

        A       B
    1  "a"    " f "
    2  "b"
    3  "c"
    4  "d"
    take colume A and combind with B1 as the spacer resulting the the string "a f b f c f d"

  2. #2
    =A1&" "&B1&" "&A2&" "&B1&" "&A3&" "&B1&" "&A4
    Code is better though as this is quite hard to see and more difficult to maintain.
    2+2=9 ... (My Arithmetic Is Mental)

  3. #3
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    273
    Location
    i was hoping excel had a function where all you had to do was pass it the two ranges and it would give you the output. if that dosen't exist then i will definetly do it in code.

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Perhaps a UDF like this.
    Function cocatinateWithDelimiter(delimiter As String, ParamArray subStrings() As Variant) As String
    Dim xVal As Variant, oneCell As Range
    For Each xVal In subStrings
        Select Case TypeName(xVal)
            Case "String", "Byte", "Integer", "Long", "Single", "Double"
                cocatinateWithDelimiter = cocatinateWithDelimiter & delimiter & CStr(xVal)
            Case "Range"
                For Each oneCell In xVal
                    cocatinateWithDelimiter = cocatinateWithDelimiter & delimiter & CStr(oneCell.Value)
                 Next oneCell
        End Select
    Next xVal
    cocatinateWithDelimiter = Mid(cocatinateWithDelimiter, Len(delimiter) + 1)
    End Function

  5. #5
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    273
    Location
    Thank you all for the help. i guess i am doing it in code.

Posting Permissions

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