«

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

48:51:29.99

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.

=-"74:02:40.29"

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

[hh]°mm'ss.00\

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 😍😍😍

=B3*24

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 *