A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.
Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.
Converts Degrees, Minutes, Seconds Format Converts Degrees, Decimal Minutes Format Converts Decimal Degrees Format Select the worksheet tab at bottom of page that matches format to convert from Select the worksheet tab at bottom of page that matches format to convert from. Note: Do not use negative numbers Note: Do not use negative numbers 0.00. Universal Transverse Mercator (UTM) UTM is conformal projection uses a 2-dimensional Cartesian coordinate system to give locations on the surface of the Earth. It is a horizontal position representation, i.e. It is used to identify locations on the Earth independently of vertical position, but differs from the traditional method of latitude.
Google Maps view of the Eiffel Tower
Utm To Decimal Degrees Converter Excel
Click the x to remove the Address box and show the interactive Google Map.
View Larger Map
Use Excel to Convert GPS Coordinates for Tom Tom
One of my destinations was to see the Eiffel Tower and Google Earth shows the GPS coordinates to be 48 degrees 51 minutes 32.64 seconds North and 2 degrees 17 minutes 34.90 seconds East. (Coordinate formatting shown as 48° 51′ 29.69″ N 2° 17′ 38.02″ E in Google Earth while holding the mouse over a position on the map.)
However the Tom Tom GPS device wanted coordinate input in degrees only, where minutes and seconds represent the fractional part. So I created a quick spreadsheet to do the conversion. (Click the picture to download the .xls file.)
To convert from Degrees, Minutes, Seconds to a Tom Tom degree format, the math is:
Degrees+(Minutes/60+Seconds/3600)
This allowed me to enter GPS coordinates directly into the Tom Tom GPS device. Since I wasn’t familiar with the street names or the numbering system for locating addresses on the Tom Tom, this was much faster than trying to find the correct address in the Tom Tom street directory. The Tom Tom GPS was a loner from an associate so that was another factor in my decision to use GPS coordinates.
Note: Tom Tom allows different formats for GPS coordinates, this example just mirrored settings for the GPS I was using at the time.
Use Excel to Convert GPS Coordinates for Garmin
When I got back home to the USA and tried this with my Garmin it didn’t work because the required format for GPS coordinates was different. My Garmin GPS device wanted Degrees and Minutes only, with seconds being the fractional part of minutes. That formula is different because it requires some concatenation with an empty space between degrees and minutes.
Degrees & ” ” & Minutes+Seconds/60
So my Excel spreadsheet helped me convert numerous GPS coordinates for my trip to Paris.
Note: If you’re wondering about the North and East designations, click this link to see how to read GPS coordinates.
Convert GPS Coordinates with Excel
Here’s an interactive worksheet that you can use to figure out some GPS coordinates for yourself.
To download the worksheet using this link.
Cross-posted at vlatte.net.
-->Note
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
Summary
Angular measurements are commonly expressed in units of degrees, minutes, and seconds (DMS). 1 degree equals 60 minutes, and one minute equals 60 seconds. To simplify some mathematical calculations, you may want to express angular measurements in degrees and decimal fractions of degrees.
This article contains a sample custom function you can use to convert a degree value stored in decimal format, to DMS stored in text format, and a sample function that converts DMS to a degree value stored in decimal format.
More Information
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Advisory Services. For more information, visit the Microsoft Web site:
Microsoft Advisory Services - https://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, see https://support.microsoft.com.
Converting Decimal Degrees to Degrees/Minutes/Seconds
The following Microsoft Visual Basic for Applications custom function accepts an angle formatted as a decimal value and converts it to a text value displayed in degrees, minutes, and seconds.
To use this function, create a conversion formula, as in the following example:
Start Excel and press ALT+F11 to start the Visual Basic editor.
On the Insert menu, click Module.
Enter the sample code for the Convert_Degree custom function described above into the module sheet.
Press ALT+F11 to return to excel.
In cell A1 type 10.46.
In cell A2 type the formula: =Convert_Degree(A1)
The formula returns 10°27'36'
Converting Degrees/Minutes/Seconds to Decimal Degrees
The following Microsoft Visual Basic for Applications custom function accepts a text string of degrees, minutes, and seconds formatted in the exact same format that the Convert_Degree function returns (for example, 10° 27' 36') and converts it to an angle formatted as a decimal value. This is exactly the reverse of the Convert_Degree custom function.
Convert Numbers To Decimal Excel
Warning
Utm To Decimal Degrees Converter Excel Converter
Utm To Decimal Degrees Formula
This custom function fails if the Degree_Deg argument is not in the format <degrees>° <minutes>' <seconds>' even if the seconds value is 0.
To use this function, create a conversion formula, as in the following example:
Start Excel and press ALT+F11 to start the Visual Basic Editor.
On the Insert menu, click Module.
Enter the sample code for the Convert_Decimal custom function described above into the module sheet.
Press ALT+F11 to return to excel.
In cell A1 type the following formula:
=Convert_Decimal('10° 27' 36'')
Note
You are required to type three quotation marks ('') at the end of the argument of this formula to balance the quotation mark for the seconds and the quotation mark for the text string. A cell reference will not require a quotation mark.
The formula returns 10.46