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
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!
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.
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.
- Extract everything to the right of "Longitude: "
- Extract everything to the left of "Latitude: " using the product of step 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 , 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.