Convert Latitude Longitude to Decimal Degrees in Excel

Have you encountered an Excel dataset with latitude and longitude values in DMS (Degrees, Minutes, Seconds) format? It can be challenging to handle, but don’t worry!

There are methods to convert the DMS values to DD (Decimal Degrees) format in Excel, the standard way of representing coordinates on most maps, geographic information systems (GIS), and other location-based tools.

Explanation of Latitude and Longitude

Latitude and longitude are a pair of geographic coordinates used to describe a location on the Earth’s surface.

Latitude measures a location’s distance from the equator, an imaginary line that circles the Earth at 0 degrees latitude.

On the other hand, longitude measures a location’s distance from the Prime Meridian. This imaginary line runs from the North Pole to the South Pole, passing through Greenwich, England, at 0 degrees longitude.

There are two commonly used formats to represent longitude and latitude:

• DMS (Degrees, Minutes, Seconds) format: Example 40° 42′ 51″ N or 74° 00′ 23″ W
• Decimal Degree format: Example – 40.72

This tutorial shows two techniques for converting latitude and longitude values from DMS format to DD.

Click here to download the example dataset

Method #1: Using Text to Columns and Formulas

In Excel, we can apply the Text to Columns Wizard to split the longitude and latitude DMS values into separate columns and then use arithmetic formulas to convert the values to decimal degrees.

Let’s consider the following dataset showing the latitudes and longitudes of ten world cities.

Our goal is to convert the latitudes listed in column B in DMS format to decimal degrees and display them in column C.

Similarly, we aim to transform the longitudes provided in column D into decimal degrees, which will appear in column E.

To achieve this, we plan to split the latitude and longitude values in DMS format into separate columns and then utilize formulas to convert the values to DD format.

Convert Latitude DMS Values to Decimal Degrees

Since we have latitude and longitude values in separate columns, we will have to separately convert them from DMS values to decimals.

To convert the latitude DMS values to decimal degrees, we need to split the Latitude DMS values into separate columns using the Text to Column Wizard, then use formulas to convert the minute values and seconds values to degrees and add the result to the degrees component.

Step #1: Split the Latitude DMS Values into Separate Columns Using the Text to Columns Wizard

We use the below steps to split the longitude DMS values into separate columns:

1. Select the cell range B2:B11 containing the latitudes and press Ctrl + C to copy.
2. On a new worksheet, press Ctrl + V to paste.
3. Select the pasted data:

4. On the Data tab, click the Text to Columns command button on the Data Tools group.

The Text Wizard correctly determines that the data is Fixed Width, meaning the fields are aligned in columns with spaces between each field.

Note: If your data is Delimited and not Fixed Width, use the method described in the next section, “Convert Longitude DMS Values to Decimal Degrees,” to separate the DMS values.

5. Click Next on the Convert Text to Columns Wizard – Step 1 of 3.
6. On the Data preview section of the Convert Text to Columns Wizard – Step 2 of 3 dialog box, click at appropriate locations to create break lines that isolate the numbers from the symbols as shown below and click Next:

7. Click Finish on the Convert Text to Columns Wizard – Step 3 of 3 dialog box:

The longitude DMS values are split into separate columns:

8. Select column B containing degree symbols by clicking its letter header, press and hold down the Ctrl key and select columns D, F, and G, which have the other characters; right-click the selection and choose Delete on the shortcut menu:

We are left with a dataset consisting solely of numerical values:

You can add column headers to the dataset as depicted below:

Step #2: Use a Formula to Convert the Latitudes in DMS to DD Format

In this step, we apply a formula in column D to convert the latitudes in DMS format to DD format using the steps below:

1. Select cell D2 and type in the following formula:

=A2+B2/60+C2/3600

1. Double-click or drag the fill handle feature in cell D2 to copy the formula down the column:

2. Copy the decimal degree values in column D and paste them as values in column C of the original dataset.

3. With the cell range C2:C11 selected, click the Decrease Decimal command button on the Number group of the Home tab several times to show the values with five decimal places:

The Decimal Degrees in column C are displayed with five decimal places.

Note: We can vary the decimal places for the latitudes depending on the precision required for the particular application or use case.

Explanation of the formula

=A2+B2/60+C2/3600

The formula first converts the minutes to decimal degrees by dividing by 60, which gives us B2/60 = 42/60 = 0.7.

Note: We divide minutes by 60 because one degree equals 60 minutes.

The formula then converts the seconds to decimal degrees by dividing by 3600, which gives us C2/3600 = 51/3600 = 0.014167.

Note: We divide the number of seconds by 3600, as one degree equals 3600 seconds.

Finally, the formula adds the degrees (40), the minutes in decimal degrees form (0.7), and the seconds in decimal degrees form (0. 014167) to obtain the latitude in decimal degrees, which is 40.71417° N.

Convert Longitude DMS Values to Decimal Degrees

To convert the longitude DMS values to decimal degrees, we need to split the DMS values into separate columns using the Text to Column Wizard, then use formulas to convert the minute values and seconds values to degrees and subtract the result from the degrees component.

Suppose we have the dataset below with longitude values in DMS format in column D:

Step #1: Split the Longitude DMS Values into Separate Columns Using the Text to Columns Wizard

We use the below steps to split the longitude DMS values into separate columns:

To begin, extract the degree values and place them in a distinct column:

1. Select the cell range A1:A10 containing the longitudes and press Ctrl + C to copy.
2. On a new worksheet, press Ctrl + V to paste.
3. Select the pasted data:

4. Select and copy the degree symbol (°) from one of the values.
5. Open the Data tab, and click the Text to Columns command button on the Data Tools group.

The Text Wizard correctly determines that the data is Delimited, meaning each column is separated by characters such as commas or tabs.

6. Click the Next button on the Convert Text to Columns Wizard – Step 1 of 3.
7. On the Delimiters option group on the Convert Text to Columns Wizard – Step 2 of 3 dialog box, select the Other option and paste the degrees symbol you copied in Step 4 on the box, and click Next:

8. Click Finish on the Convert Text to Columns Wizard – Step 3 of 3 dialog box:

The degrees symbols are removed from the degree values, and the values are placed in their distinct column:

Next, we need to segregate the minutes and seconds into separate columns.:

9. Select the cell range B1:B10:

10.  On the Data tab, click the Text to Columns command button on the Data Tools group.

The Text Wizard correctly determines that the data is Fixed Width, meaning the fields are aligned in columns with spaces between each field.

11.  Click Next.
12.  On the Data preview section of the Convert Text to Columns Wizard – Step 2 of 3 dialog box, click at appropriate locations to create break lines that isolate the numbers from the symbols as shown below and click Next:

13. Click Finish on the Convert Text to Columns Wizard – Step 3 of 3 dialog box.

The minutes and seconds components are separated into columns B and D:

14. Select columns C, E, and F containing the non-numeric symbols. Right-click the selection and click Delete on the shortcut menu:

We are left with a dataset consisting only of numerical values:

You can add column headers to the dataset as  shown below:

Step #2: Use a Formula to Convert the Longitudes in DMS to DD Format

In this step, we apply a formula in column D to convert the longitudes in DMS format to DD format using the steps below:

1. Select the cell D2 and type in the following formula:

=A2-B2/60-C2/3600

1. Double-click or drag the fill handle feature in cell D2 to copy the formula to the rest of the cells:

2. Copy the decimal degree values in column D and paste them as values in column E of the original dataset.

3. With the cell range E2:E11 selected, click the Decrease Decimal command button on the Number group of the Home tab several times to show the values with five decimal places:

The Decimal Degrees in column E are displayed with five decimal places.

Explanation of the formula

=A2-B2/60-C2/3600

The formula first converts the minutes to decimal degrees by dividing by 60, which gives us B2/60 = 0/60 = 0.

Note: We divide minutes by 60 since one degree equals 60 minutes.

The formula then converts the seconds to decimal degrees by dividing by 3600, which gives us C2/3600 = 23/3600 = 0.006389.

Note: We divide the number of seconds by 3600, as one degree equals 3600 seconds.

Finally, the formula subtracts the minutes in decimal form (0) and the seconds in decimal form (0.006389)  from the 74 degrees to obtain the longitude in decimal degrees, which is 73.99361° W.

Click here to download the example dataset

Also read: How to Convert Radians to Degrees in Excel

Method #2: Using VBA User Defined Function

We can apply user-defined functions created in Excel VBA to convert latitudes and longitudes in DMS format to DD format.

Suppose we have the following dataset showing the latitudes and longitudes of ten cities in the world in DMS format:

Our objective is to convert the latitude values recorded in column B, in DMS format, into decimal degrees and exhibit them in column C. Likewise, we aim to convert the longitude data presented in column D into decimal degrees will be displayed in column E.

Our plan involves creating user-defined functions in Excel VBA and utilizing them to convert values in DMS format to the DD format.

First, we convert the latitudes and then proceed to convert the longitudes.

How to Convert the Latitudes in DMS Format to DD Format

1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
2. Open the Insert menu and choose the Module option to insert a module.

3. Copy the following function procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function CONVERTLATDMSTODD(Degree As String) As Double
    Dim dg As Double, mn As Double, sc As Double
    
    Degree = Replace(Degree, "~", "°")
    
    dg = CDbl(Left(Degree, InStr(1, Degree, "°") - 1))
    
    mn = CDbl(Mid(Degree, InStr(1, Degree, "°") + 1, _
    InStr(1, Degree, "'") - InStr(1, Degree, "°") - 1)) / 60
    
    sc = CDbl(Mid(Degree, InStr(1, Degree, "'") + _
    1, Len(Degree) - InStr(1, Degree, "'") - 1)) / 3600

    CONVERTLATDMSTODD = dg + mn + sc
End Function

4. Save your workbook as an Excel Macro-Enabled Workbook (*.xlsm).
5. Press Alt + F11 to switch to the active worksheet.
6. Select cell C2 and type in the following formula:

=CONVERTLATDMSTODD(B2)

7. Double-click or drag the fill handle feature in cell C2 to copy the formula down the column:

How to Convert the Longitudes in DMS Format to DD Format

1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
2. Open the Insert menu and choose the Module option to insert a module.

3. Copy the following function procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function CONVERTLONDMSTODD(Degree As String) As Double
    Dim dg As Double, mn As Double, sc As Double
    
    Degree = Replace(Degree, "~", "°")
    
    dg = CDbl(Left(Degree, InStr(1, Degree, "°") - 1))
    
    mn = CDbl(Mid(Degree, InStr(1, Degree, "°") + 1, _
    InStr(1, Degree, "'") - InStr(1, Degree, "°") - 1)) / 60
    
    sc = CDbl(Mid(Degree, InStr(1, Degree, "'") + _
    1, Len(Degree) - InStr(1, Degree, "'") - 1)) / 3600

    CONVERTLONDMSTODD = dg - mn - sc
End Function

4. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
5. Press Alt + F11 to switch to the active worksheet.
6. Select cell E2 and type in the following formula:

=CONVERTLONDMSTODD(D2)

7. Double-click or drag the fill handle feature in cell E2 to copy the formula down the column:

Explanation of the User-Defined Functions

The functions take one input parameter, a string representing the DMS value of the latitude or longitude, and return a double value representing the DD value of the latitude or longitude.

The functions work as follows:

1. The first line of the function declares and initializes three double variables, dg, mn, and sc, which the function will use to hold the degree, minute, and second values of the DMS latitude or longitude.
2. The second line of the function replaces any “~” characters in the input string with the “°” character.
3. The following three lines of the function extract the degree, minute, and second values from the input string.

The degree value is obtained using the Left function to extract the characters before the first “°” character and convert the resulting string to a double using the CDbl function.

The minutes’ value is obtained using the Mid function to extract the characters between the first “°” and “‘” characters, converting the resulting string to a double using the CDbl function and dividing the result by 60 to convert from minutes to degrees.

The seconds’ value is obtained using the Mid function to extract the characters after the “‘” character, converting the resulting string to a double using the CDbl function, and dividing the result by 3600 to convert from seconds to degrees.

1. The last line of the CONVERTLATDMSTODD function calculates the DD value of the latitude by adding the minutes and seconds degree values to the degrees component. It returns the result as the output of the function.  
2. The CONVERTLONDMSTODD function’s last line calculates the longitude’s DD value by subtracting the minute and second values from the degree value. It returns the result as the output of the function.

This tutorial showed two techniques for converting latitudes and longitudes to decimal degrees in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• Convert KG to lbs (Pound) in Excel
• Convert Date to Serial Number in Excel?
• Convert Decimal to Binary in Excel
• Convert Decimal to Fraction in Excel
• Convert Bytes to MB or GB in Excel