Skip to main content
Solved

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

C
Forum|alt.badge.img+6

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!!!

Best answer by Kamille_Parks11

Cheryl_Rae wrote:

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


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.

View original
Did this topic help you find an answer to your question?

9 replies

TheTimeSavingCo
Forum|alt.badge.img+28

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})
)
Adam, Consultant @ thetimesaving.company
C
V
2 people like this
  • C
    Cheryl_Rae
  • V
    Vivid-Squid
C
Forum|alt.badge.img+6
  • Cheryl_Rae
  • Author
  • Inspiring
  • 10 replies
  • June 2, 2022
TheTimeSavingCo wrote:

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})
)

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?

Kamille_Parks11
Forum|alt.badge.img+25
Cheryl_Rae wrote:

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?


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

C
1 person likes this
  • C
    Cheryl_Rae
C
Forum|alt.badge.img+6
  • Cheryl_Rae
  • Author
  • Inspiring
  • 10 replies
  • June 2, 2022
Kamille_Parks11 wrote:

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


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

C
Forum|alt.badge.img+6
  • Cheryl_Rae
  • Author
  • Inspiring
  • 10 replies
  • June 2, 2022

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

Kamille_Parks11
Forum|alt.badge.img+25
Cheryl_Rae wrote:

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


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.

C
Hannah_Wiginton
V
3 people like this
  • C
    Cheryl_Rae
  • Hannah_Wiginton
    Hannah_Wiginton
  • V
    Vivid-Squid
C
Forum|alt.badge.img+6
  • Cheryl_Rae
  • Author
  • Inspiring
  • 10 replies
  • June 2, 2022
Kamille_Parks11 wrote:

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.


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

Kamille_Parks11
Forum|alt.badge.img+25
Cheryl_Rae wrote:

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


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.

C
Forum|alt.badge.img+6
  • Cheryl_Rae
  • Author
  • Inspiring
  • 10 replies
  • June 2, 2022
Kamille_Parks11 wrote:

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.


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

Reply


Most helpful members this week

Terms & Conditions