Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Extract latitude and longitude coordinates and put in the right order

Topic Labels: Formulas
2012 9
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello everyone,

I order to properly store geographic coordinates in Airtable, I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676”
into “12.3614676, -1.4934866”

I have tried a few formulas but I systematically failed.
Anyone could give an hand on this please? I would really appreaciate.
Warm regards from Burkina Faso

Franck

9 Replies 9

Hey Franck – this is probably a bit beyond what’s possible with Airtable’s formula field. You might be able to do something along these lines with Airtable’s newly-launched scripting block (Airtable.com/scripting), though writing such a script is a bit beyond the scope of support we’re able to offer here on the forum.

Thank you @Pete for the advice. I am not an expert, but I’ll try to dig into the scripting block.
Have a great day!

Franck

The only practical approach is with script. Even in Script Block you be unable to achieve this (natively) because you need the geo-location resources and the only way to do that is to call out to a service for this data.

We’ve built a number location-sciences integrations with Airtable using both API services (automated) and Script Blocks (which require manual invocation). Overall, our clients insist on automated solutions that simply watch for records lacking lat/lng data and our processes automatically add the properly lat/lng values in the background.

Hello Bill, thanks for the comment. To give more background, I am collecting the string "Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676” from a Jotform form fetching the Long/Lat coordinates from the form user. Once the info is collected, I push it to Airtable. But as you can see, it doesn’t appear in an exploitable format.

Ah, I should have read the entire thread more carefully. My bad. So, this is where both @Pete and I have failed you. :winking_face:

Parsing this data into adjacent formula fields (using native Airtable formulas) or into adjacent numeric fields (the approach I would typically chase) using Script Blocks are possible.

I pass this back over to @Pete to help you write a formula that extracts the lat/lng from the string example. It appears to have all the delimiters required - someone simply has to do the legwork to shape the formula.

The process is simple but very tedious using formulas.

Longitude:

  1. Extract everything to the right of "Longitude: "
  2. Extract everything to the left of "Latitude: " using the product of step 1.

Latitude:

  1. Extract everything to the right of "Latitude: "

Thanks a million Bill! I’ll try to get some help to design the formula properly. Have a great day and thanks again.

@Pete marked this solved so I assumed he helped you with this already. No?

@Bill.French – I was impressed by your solution so I marked it as solved :man_shrugging: , but we haven’t provided additional assistance here.

Well, thanks! But it wasn’t a solution.

I simply demonstrated an approach that should be relatively easy to implement using native formulas. The customer still needs a solution and this is the perfect requirement to broadly show why a no-code approach is possible in Airtable.