Converting geographic coordinates in Google Sheets

Question

I would like to convert coordinates from degrees minutes and seconds to decimal degrees in Google Sheets.

For example: 58⁰ 09' 0.5"N = 58 + 9/60 + 0.5/3600 = 58.1501389

In Google Spreadsheets there are functions like =left(E2,2)+(right(E2,7)/60). But is there a way to pick numbers based on the location between sign marks (degree ⁰, minute ') ?

For example how to find numbers between degree sign ⁰ and minute sign ' Then divide those numbers by 60?

Answer 1

Given some coordinates in the exact DMS format you've stated in A1, the following formula in B1 gives the decimal form:

=ArrayFormula(sum(split(A1,"⁰'"")N")/{1,60,3600}))

N.B. when I copy a single " character into the SPLIT delimiter argument, Sheets automatically converts it to "") for some reason. It doesn't affect the split result here though.