How do you standardize addresses in Smartsheet?

Sara S

Has anyone found a way to validate their address fields in Smartsheet? We are currently using a Smartsheet form to collect business license data and would like to integrate our data to ArcGIS to map the business locations.

Currently, we are manually updating all of the citizen-submitted addresses which is a huge time suck. Is there a way to connect to our geo-locator or some other authoritative address list we own to automate address validation?

The more we can do to clean up the addresses before the form is submitted, the better. Simple instructions in the form to tell citizens how to fill out the fields without automated intervention is not a sustainable solution for us. Thanks!

Genevieve P.

Hi @Sara S

Smartsheet Forms do not have a built-in way of validating addresses, however we may be able to help you with your process. We could potentially use a formula to join together individual fields to create the format you want, if that would help.

For example, instead of one large text box to fill in, you could ask your form users to fill in multiple, individual fields. Then in your sheet you can hide those columns and use a formula to join it together.

If you need help with this, it would be useful to see an example version of your current form (blocking sensitive data) and an example of how you need the address to be formatted.

Thanks!
Genevieve

Sara S

Thanks, @Genevieve P.. We are doing something similar now, however our larger goal is to connect the data in our sheet to ArcGIS. We are currently mapping our local businesses in GIS based off of a form they fill out in Smartsheet to apply for their business licenses. Since the ArcGIS geolocator tool runs the addresses against a pre-determined, authoritative address list, it is difficult to clean up inconsistencies without spending hours on end adjusting each individual address row.

If we had a resource to validate the data in the Smartsheet address column against our fluid list of authoritative addresses, we would save a ton of time on data cleanup.

If you have other ideas, please let me know. Otherwise, I appreciate you responding with your thoughts in the first place.

Kindly,
Sara