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?

[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!

 

[AlphaFrog]

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

Try something like this.
<br />

Book1
	A	B	C	D
1	Latitude	Longitude	Latitude (decimal degree)	Longitude (decimal degree)
2	N 29-36-28.8	W 081-11-57.2	29.60800000	81.19922222
3	N 29-36-28.6	W 081-11-57.5	29.60794444	81.19930556
4	N 29-36-06.5	W 081-14-06.7	29.60180556	81.23519444
5	N 29-36-05.0	W 081-14-05.6	29.60138889	81.23488889
6	N 29-36-04.7	W 081-14-02.1	29.60130556	81.23391667
7	N 29-36-04.5	W 081-14-09.6	29.60125000	81.23600000
Sheet1

Cell Formulas
Range	Formula
C2	=MID(A2,3,FIND("-",A2)-3)+("00:"&SUBSTITUTE(RIGHT(A2,7),"-",":"))*24
D2	=MID(B2,3,FIND("-",B2)-3)+("00:"&SUBSTITUTE(RIGHT(B2,7),"-",":"))*24

 

[Armando Montes]

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

Here is a UDF, just in case:
Convert coordinates to decimal degrees - YouTube

 

[rodwilma]

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

Thank you both so much!!

For the Longitude W, how can I make that appear as a negative? I've used the formula and it works great, but have realized I need a "-" before 81 to place it in the right location.

Thanks again!

 

[AlphaFrog]

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

Try something like...

=IF(LEFT(B2,1)="W",-1,1)*(MID(B2,3,FIND("-",B2)-3)+("00:"&SUBSTITUTE(RIGHT(B2,7),"-",":"))*24)

 

[rodwilma]

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

Perfect! Thanks so much.

 

[CassAsvestas]

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

[TABLE="width: 300"]
<tbody>[TR]
[TD]Latitude
[/TD]
[TD]Longitude
[/TD]
[/TR]
[TR]
[TD]42D 43' 5" N
[/TD]
[TD]71D 12' 37" W
[/TD]
[/TR]
[TR]
[TD]42D 36' 7.880" N
[/TD]
[TD]71D 10' 17.400" W
[/TD]
[/TR]
[TR]
[TD]42D 26' 31.65" N
[/TD]
[TD]71D 6' 16.16" W
[/TD]
[/TR]
</tbody>[/TABLE]

AlphaFrog

Do you have any formulas that may work for me to convert to decimal degrees? I have about 300 locations.

 

[AlphaFrog]

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

[TABLE="width: 300"]
<tbody>[TR]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]42D 43' 5" N[/TD]
[TD]71D 12' 37" W[/TD]
[/TR]
[TR]
[TD]42D 36' 7.880" N[/TD]
[TD]71D 10' 17.400" W[/TD]
[/TR]
[TR]
[TD]42D 26' 31.65" N[/TD]
[TD]71D 6' 16.16" W[/TD]
[/TR]
</tbody>[/TABLE]

AlphaFrog

Do you have any formulas that may work for me to convert to decimal degrees? I have about 300 locations.

I assume you wanted negative for South and West as requested before.
<br />

Book1
	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
Sheet1

Cell Formulas
Range	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)

 

[CassAsvestas]

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

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

	A	B	C	D

<colgroup><col><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: 10"]Cell[/TH] [TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10, 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: 10, 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]

Prefect! I substituted the D with the degree sign (which I couldn't figure out how to include in this text).

Thank you!
Cassandra

 

[AlphaFrog]

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

You're welcome. Thanks for the feedback.