When I export my data to excel, some information is lumped in one cell

  • Profile Image
    jofranklin
    Asked on January 11, 2018 at 05:37 AM

    Hi,

    this is my jotform

    https://eu.jotform.com/build/73394871753367

    When I export the data to excel, the name and address data items appear in separate cells exactly how I want them. Brilliant!

    But the order data 'How many teams' and the cost and 'How many book bundles' and cost appear all in one cell like this

    Number of teams you would like to enter  (Amount: 25.00 GBP, Quantity: 2)
    Number of book bundles you would like to pre-order (Amount: 100.00 GBP, Quantity: 2)
    Total: 250.00


    I need to have the quantities and amount in separate cells in excel so i can produce an invoice from them.

    can you advise either - How I change my jot form so it returns these values in different cells 

    Or

    What function I can use in excel to extract the values and put them in separate cells.

    Thanks

    Jo

  • Profile Image
    Ardian_L
    Answered on January 11, 2018 at 08:28 AM

    From how you described it, you have 2 ways to approach this:

    1. The long way - Re-create your form so it won't use the built-in products within the payment field.

    a. Use regular form fields that will serve as your products (e.g. textboxes, dropdowns, checkboxes, radio buttons).

    b. Assign Calculation Values to these fields so they can be used for tallying the total later.

    Related guide: How-to-Assign-Calculation-Value

    c. Use the Form Calculation Widget to compute the total.

    Related guide: How-to-Perform-Form-Calculation-Using-a-Widget 

    d. Then, pass the calculated result to your payment field.

    Complete guide: How-to-Pass-a-Calculation-to-a-Payment-Field 

    2. Or keep your form as is then split the details in the Payment Field within Excel.

    a. Download your submission data to Excel.

    Complete guide: How-to-Export-Form-Data-to-Excel 

    b. Afterwards, refer to these online resources on how to split data from a single cell to separate columns.

    Split-text-into-different-columns-with-the-Convert-Text-to-Columns-Wizard

    Split-data-into-several-columns-based-on-carriage-returns

    EditDelete
  • Profile Image
    Ardian_L
    Answered on January 11, 2018 at 11:53 AM

    I am sorry I am not able to understand your question. As I can see already the total is displayed £00.00

    1515689541Gbp.png

    Please explain a little bit more so we can assist you. Thank you!

  • Profile Image
    jofranklin
    Answered on January 11, 2018 at 03:00 PM

    I currently have two methods of calculating the total in the same form. The one you have pasted here is the second method and is the one that originally caused my problem with the data passing to excel. So I am developing another method above it in the same form. I'll delete the second method when I have the first one sorted.

    I would like the Total to be invoiced to be calculated only and not be able to be overwritten. When it calculates different values it no longer displays the decimal places.

    Thanks

    Jo

    1515700564jotform jo.JPG


  • Profile Image
    Marvih
    Answered on January 11, 2018 at 04:02 PM

    I would like the Total to be invoiced to be calculated only and not be able to be overwritten. When it calculates different values it no longer displays the decimal places.

    1. Please go to your Form Calculation widget settings.

    1515704217395.png

    2. Click the three dots (...) in the number pad.

    1515704300396.png

    3. Check the "Result field is read only" and "Show empty decimal places".

    1515704419397.png

    After that click the "BACK" button and click "SAVE".


    And you're done, here is the form I cloned from you https://form.jotform.com/80105739148962.


    Let us know if you need further assistance.


  • Profile Image
    jofranklin
    Answered on January 11, 2018 at 04:12 PM

    Brilliant. Thank you