Convert Latitude and Longitude

Nowadays, GPS localization is a common data. Some apps accept the decimal writing (48.85833), other return the coordinates in degree, minute, second (48°51'29.99'').

In this article you will see how to convert one writing to the other and vice-versa.

How to write a coordinate is Excel

GPS coordinates are written with the symbols generally write with the symbol ° (degree), ' (minute)'' (seconde)




But if you keep your coordinate as a string, no calculation are possible, and impossible to convert the GPS coordinate ⛔⛔⛔

What is the best way to write a GPS coordinate?

First, you must write your GPS coordinates like if it is a Time (hours, minutes, second). You just have to split part of the coordinate with a colon ":"


The display in the cell could be different in function of the version of your Excel. You can have either a display with a date or a display just with the time.

Don't worry, we will arrange 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, except if you write it like this. You write your time between double-quote like if it's a text, and before, you put the minus sign.


But the contain in the cell is just a string of #

Excel doesn't like negative time. But you can correct this by changing one option

  • 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 format of the time

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, fill exactly this code


The hour between bracket is compulsory to display value after 23.

Convert coordinate from DMS to decimal

So, because we have 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 convert is done 😍😍😍


Change the format of the range D3:E7 to General.



Convert coordinate from decimal to DMS

And the other way round, to convert a decimal value in degrees, minutes, seconds, simply divide the value by 24.
= B3/24

Permanent link to this article: https://www.excel-exercise.com/convert-latitude-longitude/

Leave a Reply

Your email address will not be published. Required fields are marked *