Nowadays, GPS localization is common. Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51'29.99'').
In this article I will show you how to convert from one format to the other and vice-versa.
Add GPS coordinates to address
If you are looking to add GPS coordinates to an address, please go to this page.
How to write a GPS coordinate in Excel
GPS coordinates are generally written using the symbols ° (degrees), ' (minutes), and '' (seconds).
But if you keep your coordinate as a string, no calculation is possible. So it will be impossible to convert the GPS coordinates ⛔⛔⛔
What is the best way to write a GPS coordinate?
Use the following format for your GPS coordinates - hours:minutes:seconds. Each part of the coordinate should be delimited with a colon ":"
The default cell display could be different depending on the version of Excel that you are using. It can display the GPS coordinates as either a date or a time.
Don't worry, we will correct this formatting soon 😉😎😃
Insert a negative time
The standard GPS coordinate is:
- positive for East and North
- negative for West and South
Unfortunately, Excel doesn't accept a negative time, so you need to wrap it in double quotes as though it were text. Then put a minus sign before the opening quote.
But the cell content is displayed as a string of #
Excel doesn't like negative time. But you can correct this by checking one option in Excel.
- Go to the menu File>Options
- Select Advanced
- At the bottom of the list of options, check the option "Use 1904 date system"
Customize the time format
Now, let's customize the format of our cell:
- Open the Format cell dialog box (Ctrl + 1)
- Select the option Custom
- In the Type box, enter this format code exactly as it is displayed below
The hour between square brackets is compulsory to display a value over 23 hours.
Convert coordinates from DMS to decimal
Because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day).
And the the conversion is finished 😍😍😍
Then just change the format of the cell range D3:E7 to General.
Convert coordinates from decimal to DMS
And the other way round, to convert a decimal value to degrees, minutes and seconds, simply divide the value by 24.