What is the formula to extract longitude and latitude from a googlemaps address

Ask the community Formulas
#1

Here is my example address:
https://www.google. com/maps/place/42+Nayland+Street,+Sumner,+Christchurch+8081,+New+Zealand/@-43.570045,172.7595125,17z/data=!3m1!4b1!4m5!3m4!1s0x6d32282318638d65:0xdbd8ceb7acf3afba!8m2!3d-43.570045!4d172.7595125

What I need to extract from over 350 records is this:

@-43.570045,172.7595125,

I was thinking i could take out everything before and after this showing up in the address but all longitudes and latitudes are different so I thought I could say maybe (?) I want to keep everything between the @ and the second comma.

thanks in advance!!!

#2

Hi Cheryl, assuming the formatting’s always the same, you could try this:

MID(
  {Field Name},
  FIND('@', {Field Name}),
  FIND(',', {Field Name}, 
    FIND(',', {Field Name}, 
      FIND('@', {Field Name})
    ) + 1
  )
  -
  FIND('@', {Field Name})
)
2 Likes
#3

Hey Adam-thank you so much for answering this. I’m not sure what field names I am supposed to be inserting though… all I have is a list of google addresses I need the break the longitude and latitude out of each one. Help?

#4

Its all the same field. Whatever you named the field that has the URL, enter that wherever Adam said “Field Name”

1 Like
#5

OMG ya’ll are amazing!!! thank you so much!!! it worked!!!

#6

Now I have a field that has (example longitude and latitude) @51.1529157,-0.4393229
I have fields set up for the examples above. how do I choose the “@ through comma” and pull that to the latitude field and everything past the comma to the longitude field so i can have it populate my map. Let me know if this is not clear. so the fields would now hold "@51.1529157, and -0.4393229 then I know how to run a substitution to take out my @ and comma

#7

Rather than doing that, forget the other formulas and just use REGEX to get the Latitude and Longitude directly.

REGEX_REPLACE({Field Name}, "(.*)@([^,]*),([^,]*)(.*)", "$2")

and

REGEX_REPLACE({Field Name}, "(.*)@([^,]*),([^,]*)(.*)", "$3")

respectively.

3 Likes
#8

You are soooo Awesome! thank you so much this really worked…

#9

Great! Can you mark the actual formula as the solution? It makes it easier in the future for people to search for the answer to similar questions.

#10

ohhhh was thinking I did…. yes i changed it to the formula!! thank you again!

#11

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.