Formula address to google maps

Jon Barto

Hello

I am having trouble finding this post. I remember seeing a post with a formula that automatically creates a link to google maps with an address pulled from a column.

Address is typed in column by street then Alt enter then city, state, zip.

I have looked high and low for the post but can't find it.

Does anyone know a formula that would work?

I have this but because of how I type the address in it (street and hit alt enter) this formula only only does the street in google maps.

="https://www.google.com/maps/search/?api=1&query=" + SUBSTITUTE([Job Address]@row, " ", "+")

Darren Mullen

@Jon Barto If you are using Alt+Enter as a separator, you'll have to have a substitute function for the carriage return, which can be found using the CHAR() function with a value of 10: CHAR(10).

="https://www.google.com/maps/search/?api=1&query=" + SUBSTITUTE([Job Address]@row, CHAR(10), "+")

Jon Barto

@Darren Mullen

I'm not sure that is working correctly..

That formula only copies the numbers into the link. The one I did copied the number and street name.

Maybe missing something?

Jon Barto

I am entering address as

123 North Street (Alt enter) Pittsburgh, Pa 15234

I am also not sure if the coma is an issue as well.

The formula I did before will work up to the coma if I keep everything on one line.

Jon Barto

@Paul Newcome

Hello Paul

I thought a couple months ago I seen you helped someone with this type of formula. I tried doing a search but cant seem to find those posts.

Would you have any idea on how to tweak this formula to work

Paul Newcome

Here is a thread that shows what each special character needs to be swapped out with (nested SUBSTITUTE functions).

https://community.smartsheet.com/discussion/100634/url-query-string-and-char-function-helper-sheet

Jon Barto

@Paul Newcome

Thanks for that link!!! That is a big help. I do have one other quick question. In my formula where would I put one of those functions?

The current formula I have one stops at the Street. So how do I account for the (alt enter) and then then coma between city and state. Address typed in cell as:

123 North Street (Alt enter)

Pittsburgh, Pa 15234

="https://www.google.com/maps/search/?api=1&query=" + SUBSTITUTE([Job Address]@row, " ", "+")

Darren Mullen

@Paul Newcome Thanks for jumping in here as well.

@Jon Barto As Paul mentioned, there are other characters you'll have to substitute. For example, you'll have to replace spaces with "%20" and for the comma, you could substitute it for an empty string represented by double quotes "".

You'll have to nest the substitute functions and they will go in the same location you have now.

Paul Newcome

You would continue with nested SUBSTITUTE functions in the same place you have your current function.

="https://www.google.com/maps/search/?api=1&query=" + SUBSTITUT(SUBSTITUTE([Job Address]@row, " ", "%20"), CHAR(10), "whatever % encoding is for line breaks")