Separate values from a single cell into each columns

  • Profile Image
    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

  • Profile Image
    EltonCris
    Answered 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. 

    If you need further assistance, let us know here.

    Regards!

  • Profile Image
    tracyalexander
    Answered on April 03, 2016 at 08:14 AM

    Thanks for this, 

  • Profile Image
    tracyalexander
    Answered on April 25, 2016 at 04: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

  • Profile Image
    Kiran
    Answered on April 25, 2016 at 05: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!