0

Seperate cells

Hi there,

within a csv.-file i've got multiple values in some cells (for example) in column A: "2, 5, 29, 39". I want to seperate these values from one column to 4 columns. Thus in column B would stand "2", in column C "5" and so on.

Is there a function that would help?

Thanks for your help!

1 comment

  • 0
    Avatar
    Janice Janczyn

    Hi Niklas,

    You can use the  ARRAY function to separate a list of comma-separated values, for example, ARRAY( 2, 5, 29, 39 ) returns

         2
         5
       29
       39

    To combine a column of comma-separated values into 1 list, you have to first JOIN the column. For example, given column A

           A
         2,5,29,39
         3,10,16

    JOIN( A:A ) returns

         2,5,29,39,3,10,16

    ARRAY( JOIN( A:A ) ) returns

         2
         5
       29
       39
         3
       10
       16

    If you want to treat each comma-separated value in your datasource's column as individual columns, you may be able to do this if each cell  in the column contains the same number of items. For example, given column B 

           B
         2,5,29
         3,10,16

    Use the SUBSTRING and INDEXOF functions in the columns of a table klip to separate the items.

         column 1
            ARRAY(SUBSTRING( A:A, 0, INDEXOF( A:A, "," ,1 ) ) )
         column 2
            ARRAY(SUBSTRING( A:A, INDEXOF( A:A, "," , 1 ) +1, INDEXOF( A:A, "," ,2) ) )
         column 2
            ARRAY(SUBSTRING( A:A, INDEXOF( A:A, "," , 2 ) +1 ) )

     

    I hope this helps,
                         Janice

     

Please sign in to leave a comment.