Use Google Maps and data from 2 columns removing State from one

We have a sheet that our Sales team uses for all sorts of data tracking. A few of the fields contain the street address, city and state information. Id like to use this information to create an additional column that has a link to Google Maps. The problem is that sometimes the sales person is including the state in the city field. What I'd like to do is remove the ,XX they've been using before passing it to the link to GM.

Something like this but this isn't working.

="https://www.google.com/maps/search/?api=1&query=" + SUBSTITUTE(Address@row, " ", "+") + SUBSTITUTE(LEFT(City@row, FIND(",", City@row) - 3)) + SUBSTITUTE(State@row, " ", "+")

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!