Nowadays, GPS localization is in common usage. Some apps use decimal format (48.85833), others return the coordinates in degrees, minutes and seconds (48°51'29.99'').
In this article I will show you how to convert one format to the other and vice-versa.
How to write a coordinate in Excel
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?
Firstly, 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 in function of the version of Excel that you are using. It can either a display the GPS coordinates as a date or a time.
Don't worry, we will format the display in 2 steps 😉😎😃
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 is 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 code exactly as it is displayed below
Convert coordinates from DMS to decimal
So, because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day).
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 in degrees, minutes, seconds, simply divide the value by 24.