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.

See Chip's stuff on latitude and longitude www.cpearson.com
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PAX_Anderson" wrote in message
...
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.

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.

ok do as mentioned below / above but to bring up the VBA editor press alt+11
not ctrl + f11

"dave" wrote:

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.