Convert Latitude and Longitude

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

48:51:29.99

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.

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

[hh]°mm'ss.00\

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

=B3*24

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.

= B3/24

Related posts


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


1 comment

    • rajeshwar pilli on 02/06/2018 at 11:07
    • Reply

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

Leave a Reply

Your email address will not be published.