Mike from the UK has thousands of records with latitude and longitude. He wants to add a hyperlink to each record that will show the location on a map. Adding hyperlinks manually will take forever. This video shows how to quickly create the hyperlinks in bulk.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1753 - Hyperlink to a Map!
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
And today's question sent in by Mike from the UK, Mike has thousands of rows of data, and each row has a latitude and longitude, and he manually built a hyperlink over here, and he got through three and then said "Oh, this is going to take forever!" Alright, so we want to be able to have a link here that will take us to a map showing that location, alright?
And so, I did a little bit of research, and the way that we would do this, here's the URL to get to maps.google.com . By the way I went to Bing Maps first, I'm a Microsoft guy, right?
But of course, Bing Maps doesn't document how to do this as well as Google Maps does.
So here we are, doing Google.
?q= and then you need to put the latitude and then a comma, a space, and then the longitude.
So check out this formula I built, it does equal F1 with the $, so it always gets that, and & to concatenate the latitude &", " and then another &B2, and when we shoot this down, it will give us the URLs.
But these of course, are not clickable.
OK, so we're going to use a function, it's been around for a while, it's called HYPERLINK.
And they want to know the link location, well hey we just built that in the formula.
And then the friendly name, I don't know, "Click here for Map") , CTRL+Enter.
Alright, now let's check it out, so this is going to be somewhere in Merritt Island, FL, you've seen that in the GeoFlow podcasts, which by the way now is Power Map.
So we click there, launches the browser, and it takes us right to, yep, that's- I recognize those streets, those are Merritt Island streets.
So it works.
These other ones here are just random, will click and see where we end up - somewhere out in the middle of water.
Alright, there you go.
Seems to be working, so you know, Mike, with thousands of these, a great way to go to just very quickly, using the hyperlink function, provided there's enough data here that you can build the URL, which if you're going to maps.google.com will certainly work, a quick way to solve that problem.
Well hey, I want to thank Mike for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1753 - Hyperlink to a Map!
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
And today's question sent in by Mike from the UK, Mike has thousands of rows of data, and each row has a latitude and longitude, and he manually built a hyperlink over here, and he got through three and then said "Oh, this is going to take forever!" Alright, so we want to be able to have a link here that will take us to a map showing that location, alright?
And so, I did a little bit of research, and the way that we would do this, here's the URL to get to maps.google.com . By the way I went to Bing Maps first, I'm a Microsoft guy, right?
But of course, Bing Maps doesn't document how to do this as well as Google Maps does.
So here we are, doing Google.
?q= and then you need to put the latitude and then a comma, a space, and then the longitude.
So check out this formula I built, it does equal F1 with the $, so it always gets that, and & to concatenate the latitude &", " and then another &B2, and when we shoot this down, it will give us the URLs.
But these of course, are not clickable.
OK, so we're going to use a function, it's been around for a while, it's called HYPERLINK.
And they want to know the link location, well hey we just built that in the formula.
And then the friendly name, I don't know, "Click here for Map") , CTRL+Enter.
Alright, now let's check it out, so this is going to be somewhere in Merritt Island, FL, you've seen that in the GeoFlow podcasts, which by the way now is Power Map.
So we click there, launches the browser, and it takes us right to, yep, that's- I recognize those streets, those are Merritt Island streets.
So it works.
These other ones here are just random, will click and see where we end up - somewhere out in the middle of water.
Alright, there you go.
Seems to be working, so you know, Mike, with thousands of these, a great way to go to just very quickly, using the hyperlink function, provided there's enough data here that you can build the URL, which if you're going to maps.google.com will certainly work, a quick way to solve that problem.
Well hey, I want to thank Mike for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
