Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Convert A Column Into Comma Delimited List

  1. #1
    Join Date
    6th February 2006
    Posts
    15

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th April 2006
    Posts
    172

    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.

    VB:
    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:

    VB:
    =csvRange(I1:I16) 
    
    
    Last edited by flee01; October 27th, 2006 at 05:31.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert A Column Into Comma Delimited List

    If you want code then you could use this which eliminates the loop.
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th April 2006
    Posts
    172

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th March 2013
    Posts
    1

    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?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Replies: 4
    Last Post: May 30th, 2008, 08:28
  2. Split Comma Delimited Single Column To Multiple Columns
    By sunny_90210 in forum EXCEL HELP
    Replies: 1
    Last Post: February 29th, 2008, 08:48
  3. Save As Comma Delimited Text File
    By badger55 in forum EXCEL HELP
    Replies: 4
    Last Post: January 14th, 2008, 22:11
  4. Pass comma-delimited list into an Array
    By cscribner in forum EXCEL HELP
    Replies: 3
    Last Post: January 28th, 2006, 08:27
  5. Replies: 3
    Last Post: March 2nd, 2005, 17:37

Bookmarks

Posting Permissions

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