Skip to main content

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

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.




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.


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: "





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.


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?


@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.


@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.


Reply