How to parsing the street field to separate fields?

  • Profile Image
    bubbbab
    Asked on August 24, 2018 at 12:04 AM

    Hi friends at Jotform!

    For this form: https://form.jotform.com/81308265600248 located as part of this page: https://www.votecollier.com/contact.htm 

    I have an address field in there which combines the street number and street name.  I've realized that I want to be able to sort Jotform reports  by the street name. It doesn't look like I can simply add a separate number field ot the pre-made address field so I will replace that address element with a series of numeric and text fields.

    The question is, can I somehow adjust the report on the existing records to separate the street number and street name?

    Weird question, I know. Thought I'd ask anyway.

    Best

    Steve...

  • Profile Image
    Kiran
    Answered on August 24, 2018 at 07:08 AM

    I am not sure if I understand your query correctly. Do you want to generate a report by having the separated street number and name from the Street address field. Unfortunately, there is no direct way to separate the field since the data is already received to your form. However, you may consider exporting the submissions to the Excel spreadsheet and use some formula to split the field to separate columns using Excel formula as below:

    153510859524082018_160852.png

    The formula to split the number i.e., in B2 cell can be =MID(A2,1,FIND(" ",A2,1)) while C2 cell can be having the formula to display the street name as =MID(A2,(FIND(" ",A2,1))+1,100).

    Please note that you might need to change the cell addresses in the formula based on the report that you have downloaded.


    Please give it a try and let us know if you need any further assistance. We will be happy to help. 

  • Profile Image
    bubbbab
    Answered on August 24, 2018 at 07:16 AM

    Thanks for that, Kiran.  I was pretty sure I couldn't. The pre-made address element that I'm using, can I separate the street number and street name on that element or do I have to create an address cluster made of all the different fields to achieve this?

    Thanks again,

    Steve...

  • Profile Image
    Kiran
    Answered on August 24, 2018 at 08:29 AM

    If you want to receive the data separately for the new submissions, you may consider enabling the option Street Address 2 from the Options tab and change the Sublabels of Street address to Street Number and Street Name as shown below:

    153511373924082018_170844.png

    153511376624082018_170833.png

    Hope this information helps! 

  • Profile Image
    bubbbab
    Answered on August 27, 2018 at 11:37 PM

    Thank you for that excellent advice! I have done that. One final thing, is there a way to make the street number in that address cluster into numeric only?

    Thanks, once again for the great support!

  • Profile Image
    Kiran
    Answered on August 28, 2018 at 01:12 AM

    is there a way to make the street number in that address cluster into numeric only?

    I am sorry to let you know that it is not possible to make the street number field to accept only numbers. If you want the users to input only numbers, it is only possible by adding the numeric field or Short text input field with numeric validation.

    Thanks!