Delimter used in Excel export to separate payment data into multiple columns

  • Profile Image
    Denise Brown 
    Asked on April 08, 2016 at 01:27 PM

    Hello,

    When we export JotForm data to Excel, all of the "Overhead Items" selected appear in a single cell in the Excel spreadsheet, but have a line break inside of the text.  I am trying to to a text to columns operation on the cell, but do not know what delimiter character is used to create the line break.  Can you please advise?  Thanks you!

    Denise

  • Profile Image
    Ben
    Answered on April 08, 2016 at 02:45 PM

    If the new line is added then you would be able to match it with \n through regex. The easiest way to do it without regex would be to use the following function:

    =SUBSTITUTE(A1,CHAR(13) & CHAR(10), ",");

    It will then replace the new line with comma. You can of course change this into anything that you wish so that you can use it in other functions as well.

    A1 should be changed to match the cell where the new lines are in.

    Do of course let us know if you have any additional questions.

  • Profile Image
    Denise Brown 
    Answered on April 08, 2016 at 03:45 PM
    Hi Ben,
    Your suggestion works, but unfortunately doesn't solve my problem. Perhaps
    you can offer a suggestion for how I can resolve our issue.
    ...
  • Profile Image
    Mike
    Answered on April 08, 2016 at 05:10 PM

    This is not likely that we can use some specific delimiter in this case, but there is a workaround provided on the next support forum thread.

    Submissions EXCEL/CSV reports: Separating payment info or products into each column

     

    If you need any further assistance, please let us know.