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

cbsarge

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, " ", "+")

Genevieve P.

Hi @cbsarge

Will the State cell be blank in this instance, or do the Sales team enter the State in twice?

You could have an IF statement to create one type of link if there's a comma in the City cell, or another link if there's no comma:

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

Let me know if this makes sense and works for you!

Cheers,

Genevieve