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

  • jofranklin
    Asked on January 11, 2018 at 5: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

  • Ardian_L
    Replied on January 11, 2018 at 8: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
  • jofranklin
    Replied on January 11, 2018 at 11:40 AM

    Thank you that is very helpful.

    I have done it but would like to make the calculated amount as a display only rather than a field that user can overwrite.

    I would also like it to be displayed as the value with decimal point and £ and 00 ie £00.00 as it is a money value.

    Is that easy?

  • Ardian_L
    Replied 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 Screenshot 10

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

  • jofranklin
    Replied on January 11, 2018 at 3: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 Screenshot 10


  • Marvih
    Replied on January 11, 2018 at 4: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.

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

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

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

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

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

    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.


  • jofranklin
    Replied on January 11, 2018 at 4:12 PM

    Brilliant. Thank you