Announcement

Collapse
No announcement yet.

Convert A Column Into Comma Delimited List

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert A Column Into Comma Delimited List

    Hey all.

    Back once again with a new problem.

    So here is my deal.

    I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.

    Any suggestions?

    What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.

    Example:

    The list (starting in A1) looks like this:
    Dog
    Cat
    House
    Car
    Boat
    Mom
    Dad

    but I need:
    Dog,Cat,House,Car,Boat,Mom,Dad

    If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.

    Thanks!

  • #2
    Re: Convert A Column Into Comma Delimited List

    Instead of using the CONCATENATE function, which I assume you are, use the & operator.

    In B2 put =A1 & "," & A2

    In B3 put =B2 & "," & A3

    Then copy down.
    Boo!

    Comment


    • #3
      Re: Convert A Column Into Comma Delimited List

      Create your own function to loop thru the values and concatenate them. If you know how to insert a module, put this in one.

      Code:
      Function csvRange(myRange As Range)
          Dim csvRangeOutput
          For Each entry In myRange
              csvRangeOutput = csvRangeOutput & entry.Value & ", "
          Next
          csvRange = csvRangeOutput
      End Function
      Put this in the cell to grab the first 16 cells of column I:

      Code:
      =csvRange(I1:I16)
      Last edited by flee01; October 27th, 2006, 04:31.

      Comment


      • #4
        Re: Convert A Column Into Comma Delimited List

        If you want code then you could use this which eliminates the loop.
        Code:
        Function csvRange(myRange As Range)
        
            csvRange = myRange
            
            csvRange = Application.Transpose(csvRange)
            
            csvRange = Join(csvRange, ",")
            
        End Function
        Note this will only work if the range passed to the function is only 1 column.

        If you are dealing with multiple columns use flee01's code.
        Boo!

        Comment


        • #5
          Re: Convert A Column Into Comma Delimited List

          To get the text you'll have to copy then Paste Special > Paste:values in excel or you can always just paste it into notepad.

          Comment


          • #6
            Re: Convert A Column Into Comma Delimited List

            How do I do it the other way around? I want to take a list of words separate by a comma in a single cell to populate into a column with each word without the comma presented. More important, I want to do the following:


            A1 contains: "apples, oranges, peaches"
            A2 contains: "yellow, black, blue"


            I want to put each word (without the comma) into a single cell all in one column:

            apples
            oranges
            peaches
            yellow
            black
            blue


            How do I do this?

            Comment

            Working...
            X