Excel Question - GPS

[Lumberjack93]

Can anyone help me create a formula or script that will convert "Decimal Degrees" to "Degrees and Decimal Minutes"?


Here is a formula that converts Decimal Degrees to Degrees-Minutes-Seconds. However, I need it converted to Decimal Degrees.

Assuming your number is A1, try the following

=INT(A1)&CHAR(186)&" "&INT((A1-INT(A1))*60)&CHAR(145)&" "&ROUND((((+A1-INT(A1))*60)-INT((+A1-INT(A1))*60)),2)*60&CHAR(145)

Latitude and Longitude Formats

Format Example Usage Details

Decimal Degrees 35.73972222 GIS Applications The degrees are listed as the integer portion of the number. Any minutes and seconds are converted to their decimal equivalent and added to the degrees. Since there are 60 minutes in a degree, you divide minutes by 60. Since there are 60 seconds in a minute or 3600 seconds in a degree, you divide seconds by 3600. No hemisphere is listed. The northern and eastern hemispheres are considered positive numbers and the southern and western hemispheres are considered negative numbers.

Degrees Minutes Seconds 35° 44' 23" N The Layperson The degrees are listed as the integer portion of the number. Any minutes and seconds are converted to their decimal equivalent and added to the degrees. Since there are 60 minutes in a degree, you divide minutes by 60. Since there are 60 seconds in a minute or 3600 seconds in a degree, you divide seconds by 3600. Usually the hemisphere is listed after the number.

Degrees and Decimal Minutes 35 44.38333 N GPS Devices The degrees are typically listed by themselves. The minutes and seconds are listed in decimal format with the minutes being the integer portion and the seconds being the fractional portion of the number. Any seconds are divided by 60 to be added to the minutes in this format. Usually the hemisphere is listed after the number.

Any help would be much appreciated.

Thanks

Lumberjack93

 

[Lumberjack93]

BTW, any help would be greatly appreciated.

Thanks

Lumberjack93

 

[IML]

with decimal degrees in A1, you could use
=INT(A1)+(MOD(A1,1)*60)/100
to retain a true number, or

=INT(A1)&" "&TEXT((MOD(A1,1)*60),"0.00000 ")&IF(A1>0,"N","S")

Edit to show southern/eastern hemi as a postive
=ABS(INT(A1))&" "&TEXT((MOD(A1,1)*60),"0.00000 ")&IF(A1>0,"N","S")

 

[Oaktree]

Welcome to the board!

=INT(A1)&CHAR(186)&" "&(INT((A1-INT(A1))*60)+(((+A1-INT(A1))*60)-INT((+A1-INT(A1))*60)))

 

[Lumberjack93]

Sorry, but those didn't work.


35.73972222 in Decimal Degrees should convert to:

35 44.38333 in Degrees and Decimal Minutes.


This website will confirm the conversion. However, you can only do one conversion at a time on the website. That's why I would like to have an equivalent conversion tool in Excel.

http://www.terraserver.com/tools/degrees_converter.asp

Thanks again for the welcome to the board and for any expert advice.

Lumberjack93

 

[Oaktree]

Isn't that what my formula returns?

Book4
	A	B	C	D
1	35.73972222	3544.3833331999998
Sheet1

I also get 35 44.38333 N from IML's =INT(A1)&" "&TEXT((MOD(A1,1)*60),"0.00000 ")&IF(A1>0,"N","S") formula

 

[Lumberjack93]

Oaktree, thanks mucho for the help. It worked like a charm.

I must have made a mistake when I first tried it.

Thanks

Lumberjack93

 

[zraj07]

converting

How would this formula be changed so you would input Degrees, Decimal Minutes and get back Decimal Degrees?

 

[Oaktree]

Something like:

=LEFT(A1,SEARCH(CHAR(186),A1)-1)+RIGHT(A1,LEN(A1)-SEARCH(" ",A1))/60

Book9
	A	B	C	D
1	35 44.3833331999998	35.73972222
Sheet1

 

[zraj07]

So you are seraching for the character code for Degree and splitting that out from the "text" value in A1, and then dividing the remaining values by 60.

I went looking for the 186 character code in Characater Map, but not finding what I expected led me to search the web; there I found character code for Degree was 176.

Yet the char code of 186 that you specify (which is really the masculine ordinal version of the degree symbol) seems to work.

Any idea of why that is so ?