press Ctrl + F11 to open the VBA editor


add a module and in the new module, add this code


Public Function convertGPStoDouble(str As String) As Double

convertGPStoDouble = CDbl(Replace(str, ":", ""))

End Function


in the spreadsheet, store the gps as text (i.e. in cell A1) , and in the
cell next to it (B1) type =convertGPStoDouble(A1)
this will give you your number value.

you will need to have macros enabled to use this.


"PAX_Anderson" wrote:

I need to input raw formatted GPS coordinates into a spreadsheet i.e.
Latitude and Longitude.

Excel gives no options to allow the required formats, which can include
negative values. The formats I need a

Latitude: 00:00.000 to 89:59.999, plus 90:00.000

Longitude: 000:00.000 to 179:59.999, plus 180:00.000

Both value sets can be positive & negative values. I've tried using
hhh:mm.000 and Excel rejects it when I try to make the value. The only thing
I have been able to do is handle as text. Which ends any attempts to run
calculation.

The party that really stymies me is that apparently this never came up before.