Extract latitude and longitude coordinates and put in the right order

[Maia_Africa]

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

[Pete]

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.

[Maia_Africa]

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

[Bill_French]

Maia_Africa:

I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676”
into “12.3614676, -1.4934866”

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.

[Maia_Africa]

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.

[Bill_French]

Maia_Africa:

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

[Maia_Africa]

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

[Bill_French]

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

[Pete]

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

Pete:

I was impressed by your solution

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.