Convert Degree Min Sec to Decimal Degree Lat/Longitude

[rodwilma]

I have a spreadsheet with coordinates in the following format and I need to convert these to decimal degrees. Anyone know how?

Latitude </SPAN></SPAN>	Longitude </SPAN></SPAN>
N 29-36-28.8</SPAN></SPAN>	W 081-11-57.2</SPAN></SPAN>
N 29-36-28.6</SPAN></SPAN>	W 081-11-57.5</SPAN></SPAN>
N 29-36-06.5</SPAN></SPAN>	W 081-14-06.7</SPAN></SPAN>
N 29-36-05.0</SPAN></SPAN>	W 081-14-05.6</SPAN></SPAN>
N 29-36-04.7</SPAN></SPAN>	W 081-14-02.1</SPAN></SPAN>
N 29-36-04.5</SPAN></SPAN>	W 081-14-09.6</SPAN></SPAN>

<TBODY>
</TBODY>

Any input is greatly appreciated.

Thanks!

 

[stephfonder]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

AlphaFrog,
I have the same problem with data formatted 42:43:5N 139:55:31S. Trying to modify your original solution, I had trouble because the 1st part could be 1-3 characters - it isn't fixed length. I was easily able to modify what you did below and just multiply by the IF portion. However, I don't see how multiplying by 24 or -24 creates the result that it does! Happy that it just seems to work, but I'm curious how. I'm always suspicious of possible errors if I don't understand the underlying mechanism. "XXX:YY:ZZ" must be converted to a number somehow? Why 24?

Stephanie

I assume you wanted negative for South and West as requested before.

	A	B	C	D
1	Latitude	Longitude	Latitude (decimal degree)	Longitude (decimal degree)
2	42D 43' 5" N	71D 12' 37" W	42.71805556	-71.21027778
3	42D 36' 7.880" N	71D 10' 17.400" W	42.60218889	-71.1715
4	42D 26' 31.65" N	71D 6' 16.16" W	42.442125	-71.10448889

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Worksheet Formulas Cell Formula C2 =SUBSTITUTE(SUBSTITUTE(LEFT(A2,LEN(A2)-3),"D ",":"),"' ",":")*IF(RIGHT(B2,1)="S",-24,24) D2 =SUBSTITUTE(SUBSTITUTE(LEFT(B2,LEN(B2)-3),"D ",":"),"' ",":")*IF(RIGHT(B2,1)="W",-24,24) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlphaFrog]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

"XXX:YY:ZZ" must be converted to a number somehow? Why 24?

Degree:Minute:Second is very nearly the same as time Hour:Minute:Second. Excel will recognize this text format "XXX:YY:ZZ" as Time when used in a calculation. So one part of the formula converts the text Degree:Minute:Second into a text format that Excel will eventually recognize as time or as you put it "XXX:YY:ZZ".

Serial Date-Time in Excel is a numeric value where the integer portion of the value represents day(s) and the decimal represents a portion of one day or (Hour:Minute:Second). This link better explains how Excel stores Serial Dates and Time.

So why multiply by 24? To convert the Serial Time's decimal hours into integer hours. In other-words; this serial time days.tttttt numeric value is converted to hours.tttttt if that makes sense.

 

[vanguardutilities]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

I need help on this too, please. I need to convert DMS like this -953611.61 into decimal. Could you provide me with a formula?

Example: -953611.61 = -95 degrees, 36 minutes, 11.61 seconds

 

[shg]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

=(INT(ABS(A1)/10000) + INT(MOD(ABS(A1), 10000)/100) / 60 + MOD(ABS(A1), 100) / 3600) * IF(A1<0, -1, 1)

Or

=TEXT(ABS(A1), "0\:00\:00.00") * IF(A1<0, -24, 24)

 

[vanguardutilities]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

=(INT(ABS(A1)/10000) + INT(MOD(ABS(A1), 10000)/100) / 60 + MOD(ABS(A1), 100) / 3600) * IF(A1<0, -1, 1)

Or

=TEXT(ABS(A1), "0\:00\:00.00") * IF(A1<0, -24, 24)

Thank you so much, shg! You're a real life saver! We postprocess GPS points and needed this badly to save us hours and hours of conversion labor.

 

[shg]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

You're welcome.

 

[Dnicol98]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

Hi I need help as well. Can someone please show me how to convert the following to decimal degrees? My data is in the following format:

Latitude Longitude
22°36'43.7"N 120°16'23.1"E
22°27'58.1"N 113°53'00.4"E
22°18'52.1"N 114°12'46.2"E
16°56'37.6"N 54°00'06.0"E
40°40'04.7"N 74°01'00.2"W

Thank you in advance!

 

[shg]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

Welcome to the board.

	A​	B​	C​	D​
1​	Latitude​	Longitude​	Latitude​	Longitude​
2​	22°36'43.7"N	120°16'23.1"E	22.612139​	120.273083​
3​	22°27'58.1"N	113°53'00.4"E	22.466139​	113.883444​
4​	22°18'52.1"N	114°12'46.2"E	22.314472​	114.212833​
5​	16°56'37.6"N	54°00'06.0"E	16.943778​	54.001667​
6​	40°40'04.7"N	74°01'00.2"W	40.667972​	-74.016722​

The formula in C2 and copied down and right is

=IF(OR(RIGHT(A2)={"S","W"}), -24, 24) * TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2, LEN(A2)-1), "°", ""), "'", ""), """", ""), " ", ""), "0\:00\:00.0")

 

[Dnicol98]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

Beautiful. Thank you so much!

 

[shg]

Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

You're welcome.