Convert Degree Min Sec to Decimal Degree Lat/Longitude

rodwilma · Nov 12, 2012

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

[TABLE="width: 282"]
<TBODY>[TR]
[TD]Latitude </SPAN></SPAN>
[/TD]
[TD]Longitude </SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-28.8</SPAN></SPAN>
[/TD]
[TD]W 081-11-57.2</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-28.6</SPAN></SPAN>
[/TD]
[TD]W 081-11-57.5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-06.5</SPAN></SPAN>
[/TD]
[TD]W 081-14-06.7</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-05.0</SPAN></SPAN>
[/TD]
[TD]W 081-14-05.6</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-04.7</SPAN></SPAN>
[/TD]
[TD]W 081-14-02.1</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-04.5</SPAN></SPAN>
[/TD]
[TD]W 081-14-09.6</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Any input is greatly appreciated.

Thanks!

stephfonder · Dec 2, 2014

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

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

A B C D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Latitude[/TD]
[TD="align: center"]Longitude[/TD]
[TD="align: center"]Latitude
(decimal degree)[/TD]
[TD="align: center"]Longitude
(decimal degree)[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]42D 43' 5" N[/TD]
[TD="align: center"]71D 12' 37" W[/TD]
[TD="align: center"]42.71805556[/TD]
[TD="align: center"]-71.21027778[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]42D 36' 7.880" N[/TD]
[TD="align: center"]71D 10' 17.400" W[/TD]
[TD="align: center"]42.60218889[/TD]
[TD="align: center"]-71.1715[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]42D 26' 31.65" N[/TD]
[TD="align: center"]71D 6' 16.16" W[/TD]
[TD="align: center"]42.442125[/TD]
[TD="align: center"]-71.10448889[/TD]

</tbody>

Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10px, bgcolor: #E0E0F0"]C2[/TH]
[TD="align: left"]=SUBSTITUTE( SUBSTITUTE(LEFT(A2,LEN(A2)-3),"D ",":"),"' ",":" )*IF( RIGHT(B2,1)="S",-24,24 )[/TD]

[TH="width: 10px, bgcolor: #E0E0F0"]D2[/TH]
[TD="align: left"]=SUBSTITUTE( SUBSTITUTE(LEFT(B2,LEN(B2)-3),"D ",":"),"' ",":" )*IF( RIGHT(B2,1)="W",-24,24 )[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

AlphaFrog · Dec 2, 2014

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 · Apr 1, 2015

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 · Apr 1, 2015

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 · Apr 1, 2015

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

shg said:
=(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 · Apr 1, 2015

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

You're welcome.

Dnicol98 · Aug 1, 2016

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 · Aug 1, 2016

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

Welcome to the board.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][td="bgcolor:#C0C0C0"]

C

[/td][td="bgcolor:#C0C0C0"]

D

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td="bgcolor:#F3F3F3"]

Latitude

[/td][td="bgcolor:#F3F3F3"]

Longitude

[/td][td="bgcolor:#F3F3F3"]

Latitude

[/td][td="bgcolor:#F3F3F3"]

Longitude