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!

 

[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

Latitude	Longitude
42D 43' 5" N	71D 12' 37" W
42D 36' 7.880" N	71D 10' 17.400" W
42D 26' 31.65" N	71D 6' 16.16" W

<tbody>
</tbody>

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

Latitude	Longitude
42D 43' 5" N	71D 12' 37" W
42D 36' 7.880" N	71D 10' 17.400" W
42D 26' 31.65" N	71D 6' 16.16" W

<tbody>
</tbody>

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

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.