«

»

Convert Latitude and Longitude

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

GPS coordinates are generally written using the symbols ° (degrees), ' (minutes)'' (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?

Firstly,  use the following format for your GPS coordinates - hours:minutes:seconds. Each part of the coordinate should be delimited with a colon ":"

48:51:29.99

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.

=-"74:02:40.29"

But the cell content is displayed as 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 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

[hh]°mm'ss.00\

The hour between square brackets is compulsory to display a value over 23.

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).

And the the conversion is done 😍😍😍

=B3*24

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.
= B3/24

Related articles


Have a look at these other articles that could help you in your work

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 *