Formula address to google maps
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, " ", "+")
Answers
-
@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), "+")
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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?
-
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.
-
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
-
Here is a thread that shows what each special character needs to be swapped out with (nested SUBSTITUTE functions).
-
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, " ", "+")
-
@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.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!