# Excel Question - GPS

#### Lumberjack93

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
BTW, any help would be greatly appreciated.

Thanks

Lumberjack93

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

Welcome to the board!

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

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

Isn't that what my formula returns?
Book4
ABCD
135.739722223544.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

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

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

Thanks

Lumberjack93

converting

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

Something like:

=LEFT(A1,SEARCH(CHAR(186),A1)-1)+RIGHT(A1,LEN(A1)-SEARCH(" ",A1))/60
Book9
ABCD
135 44.383333199999835.73972222
Sheet1

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 ?

Replies
2
Views
650
Replies
2
Views
235
Replies
7
Views
2K
Replies
2
Views
328
Replies
1
Views
841

### Forum statistics

1,207,436
Messages
6,078,546
Members
446,346
Latest member
shinbum ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back