Separate values from a single cell into each columns

  • MichaelBrightman
    Asked on July 30, 2014 at 11:14 AM

    On our survey a number of questions tell the person filling it out to tick all boxes that apply.  When the information downloads the answer for that question is all on one line in excel and it cannot be separated out into separate columns easily because there is no delimiter or comma as such. Is there a way to download answers to questions where multiple boxes have been ticked that solves this problem?

    Thanks in anticipation

  • Elton Support Team Lead
    Replied on July 30, 2014 at 12:28 PM

    Hi,

    Unfortunately, they're configured to display in that manner. However, you can try the following workaround on how to place them into separate columns using excel formula.

    Actually, you can add delimiters on each options per line since each option are brought down into new lines. You can use the following formula. This will add ^ symbol as delimiter on each option per line. You can drag down to automatically apply it on the other cells.

    =SUBSTITUTE(A1,CHAR(10),"^")

    After that, highlight the column containing the formula, then go to Data --> Text to Columns. When the Wizard comes up, select Delimited on the first screen and Other on the next screen. In the blank space next to Other, insert ^. Click Finish, and your products should be listed in separate columns. 

    Separate values from a single cell into each columns Image 1 Screenshot 30

    Separate values from a single cell into each columns Image 2 Screenshot 41

    If you need further assistance, let us know here.

    Regards!

  • tracyalexander
    Replied on April 3, 2016 at 8:14 AM

    Thanks for this, 

  • tracyalexander
    Replied on April 25, 2016 at 4:10 PM

    Hi

     

    I cant get this to work as when I pull it into excel there is nothing between the end of one entry and the start of another so how do I get excel to see that there is actually 2 entries in this colum

    Separate values from a single cell into each columns Image 1 Screenshot 20

  • Kiran Support Team Lead
    Replied on April 25, 2016 at 5:26 PM

    @tracyalexander,

    Your question is moved to a separate thread and shall be answered there shortly.

    https://www.jotform.com/answers/826201

    Thank you!