I am trying to parse the address elements out of the autocomplete address widget

  • Profile Image
    GSNYPENN
    Asked on August 03, 2017 at 04:10 PM

    We are using your autocomplete Address widget -- as that is the best way to get people to fill out our form (and we anticipate thousands of responses!)

    We are attempting to parse out the addresses so that we can mail people their tickets, but for some reason, this isn't working the way a normal string works. When I use the colon delimited in Excel, I can only get it to recognize the first field or two. Everything else disappears!

    I've tried Excel, CSV, tables -- I am truly flummoxed! Is there a way you can help me with this? Can we export individual address fields as in street address, city, state, zip code so that I can use this on an envelope?

     

    Thank you in advance!

     

    Wendy

  • Profile Image
    Welvin
    Answered on August 03, 2017 at 05:33 PM

    I'm sorry to say that there is no way you can set to separate the auto complete address inputs into different cells in the excel file. 

    You may consider getting this extension to extract the inputs: https://www.extendoffice.com/download/kutools-for-excel.html

    Their split to columns option may help you:

    https://www.extendoffice.com/documents/excel/3448-excel-split-cell-contents-into-columns.html 

    Other than this, it's manual. 

  • Profile Image
    Wendy
    Answered on August 20, 2017 at 12:02 PM

    For anyone following this, I have the answer.

    Right after I posted this, it hit me: there are invisible characters in the line.

    I used the old CLEAN function to remove the carriage returns from the code.  =CLEAN(cell ref)

    Once I cleaned the cell, it was easy to use "Text to Column" because there is a delimited in the cell between the address elements.

    Unfortunately, we had a problem with that call to the Google API and wound up taking it down as it failed too many times for our customers.

    I did go to Google and get an authentication code -- and that fixed the problem temporarily, but it is really wonky. Many people complained that they could not enter their address information.

    We need to investigate this thoroughly before we do this again. We had lots of traffic and many complaints about this part of the form. 


  • Profile Image
    Mike_G
    Answered on August 20, 2017 at 01:20 PM

    In addition to what my colleague, Welvin, and @Wendy have suggested, you can also check and try to follow the instructions below if you want to make the data from the Autocomplete Address widget separated into columns in an excel file.

    Supposed you have already downloaded the excel file of your form submissions — How-to-Partially-Download-Form-Submissions-as-Excel-CSV-PDF

     — Select the entire column that you want the values separated into columns.

    Note: Make sure there is enough space on the right of that column. If there isn't please insert the necessary columns.

     — Click Data from the menu and click the Text to Columns button.

     — Then, on the Convert Text to Column Wizard window, select "Delimited" as the Original Data Type and click the Next button.

     — Uncheck the "Tab" Delimiter and select "Other:"

     — In the "Other Box", press CTRL+J from your keyboard. CTRL+J represents a return carriage and the address in the Autocomplete Address widget is separated by a new line.

     — After doing that, click Next and click Finish on the next page.

    I hope this helps. If you have other questions or concerns, please feel free to contact us again anytime.