3 Easy Ways to Convert Coordinates to DMS in Google Sheets

2025年1月21日

Have you ever found decimal format coordinates (e.g., 35.685085) hard to interpret?

Converting them into Degrees, Minutes, and Seconds (DMS) format (e.g., 35° 41′ 6.31″) can make the data much easier to read and simplify tasks like mapping or verifying location information.

In this article, we’ll explore 3 methods to convert latitude and longitude into DMS format using Google Sheets:

Using formulas
Creating a custom function
Utilizing Google Apps Script (GAS)

Choose the method that best suits your needs and data size!

Comparison of Methods to Convert Latitude and Longitude to DMS Format

Method	Features	Best Use Cases
Formulas	Simple to use Requires input for each cell manually	Ideal for quickly converting small datasets Suitable if you prefer not to use scripts
Custom Functions	Easy to implement with a simple script Requires input for each cell	Useful for repetitive tasks across multiple sheets
Scripts	Capable of processing large datasets in one go Fully automated	Efficient when dealing with large datasets Ideal for automating regular processing

What is DMS Format?

DMS (Degrees, Minutes, Seconds) is a format used to represent latitude and longitude as follows:

Degrees (°): The integer part of the decimal value.
Minutes (′): The decimal part of the degrees multiplied by 60, taking only the integer part.
Seconds (″): The decimal part of the minutes multiplied by 60.

例:

Decimal Format: 35.685085

DMS Format: 35° 41′ 6.31″

Method 1: Converting to DMS Format Using Formulas

You can easily convert latitude and longitude to DMS format using Google Sheets formulas.

Formulas

For Latitude (e.g., cell B2):

=INT(B2) & "° " & INT((B2-INT(B2))*60) & "′ " & ROUND(((B2-INT(B2))*60-INT((B2-INT(B2))*60))*60, 2) & "″"

For Longitude (e.g., cell C2):

=INT(C2) & "° " & INT((C2-INT(C2))*60) & "′ " & ROUND(((C2-INT(C2))*60-INT((C2-INT(C2))*60))*60, 2) & "″"

Explanation of the Formula

INTFunction:Extracts the integer part of the number by discarding the decimal part.

INT(B2): Retrieves the degrees (°).
(B2-INT(B2))*60: Calculates the minutes (′) by taking the decimal part of the degree (B2 – INT(B2)) and multiplying it by 60.
((B2-INT(B2))*60-INT(...))*60: Computes the seconds (″) by taking the decimal part of the minutes and multiplying it by 60.
The ROUND function is used to round the result to 2 decimal places for better readability.

Method 2: Converting to DMS Format Using a Custom Function

By using a custom function, you can directly convert latitude and longitude into DMS format within Google Sheets cells. This method simplifies the process and makes your formulas more concise.

Custom Function Formulas

For Latitude (e.g., cell B2):

=DMS(B2)

For Longitude (e.g., cell C2):

=DMS(C2)

Steps to Set Up the Custom Function

To use a custom function, follow these steps:

STEP

Open Apps Script Editor

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

STEP

Create the Script

Since the default function myFunction() {} is present, delete it and replace it with the script below.

function DMS(value) {
  const degrees = Math.floor(value); // Extract the degrees (°)
  const minutes = Math.floor((value - degrees) * 60); // Calculate the minutes (′)
  const seconds = ((value - degrees) * 60 - minutes) * 60; // Calculate the seconds (″)
  return degrees + "° " + minutes + "′ " + seconds.toFixed(2) + "″"; // Format the result as DMS
}

Script Description

Math.floor(value): Retrieves the degrees (°) by truncating the decimal part.
(value - degrees) * 60: Multiplies the decimal part by 60 to calculate the minutes (′).
(minutesの小数部分 * 60): Calculates the seconds (″) and rounds it to two decimal places.

STEP

Save the Script

After writing the script, give it a name and save it.

Example: “Coordinates (DMS Format)”

STEP

Run the Script

Run the script once to authorize the required permissions.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

Once the setup is complete, enter the custom function in a cell to use it.

Method 3: Bulk Conversion with Google Apps Script

If you need to convert a large amount of data at once, using a script is the most efficient approach.

The following script converts latitude (column B) and longitude (column C) into DMS format and outputs the results in columns D and E, respectively.

Key Features:

The original data in columns B (latitude) and C (longitude) remains unchanged.
Column D: Converted latitude (DMS format).
Column E: Converted longitude (DMS format).
The script refers to a sheet named “GeoData” when executed.

Steps to Execute Google Apps Script

STEP

Open Apps Script Editor

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

STEP

Create the Script

Since the default function myFunction() {} is present, delete it and replace it with the script below.

function convertToDMS() {
  // Get the sheet named "GeoData"
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GeoData");
  
  // If the sheet does not exist, show an alert and stop execution
  if (!sheet) {
    SpreadsheetApp.getUi().alert('The sheet named "GeoData" was not found. Please check the sheet name.');
    return;
  }
  
  // Define the range for latitude (column B) and longitude (column C) starting from row 2
  const range = sheet.getRange(2, 2, sheet.getLastRow() - 1, 2); 
  const values = range.getValues();

  // Create an array to store the DMS format results for columns D and E
  const output = values.map(row => {
    const lat = row[0]; // Latitude
    const lng = row[1]; // Longitude
    return [
      lat ? convert(lat) : "", // Convert latitude to DMS (column D)
      lng ? convert(lng) : ""  // Convert longitude to DMS (column E)
    ];
  });

  // Write the results to columns D (4th column) and E (5th column)
  sheet.getRange(2, 4, output.length, 2).setValues(output);
}

// Function to convert decimal format to DMS format
function convert(value) {
  const degrees = Math.floor(value); // Degrees (°)
  const minutes = Math.floor((value - degrees) * 60); // Minutes (′)
  const seconds = ((value - degrees) * 60 - minutes) * 60; // Seconds (″)
  return degrees + "° " + minutes + "′ " + seconds.toFixed(2) + "″";
}

Script Description

Accessing the Sheet

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GeoData");

Specify the sheet name to retrieve the target sheet.
If you need to use a different sheet, replace 'GeoData' with the actual sheet name (e.g., 'Address List').

Retrieving Data from the Sheet

  const range = sheet.getRange(2, 2, sheet.getLastRow() - 1, 2); 
  const values = range.getValues();

2, 2: Starts data retrieval from the 2nd row and the 2nd column (B column).
sheet.getLastRow() - 1: Calculates the number of rows to retrieve, from the 2nd row to the last row.
2: Retrieves data from two columns (B and C).

Latitude and Longitude DMS Conversion

  const output = values.map(row => {
    const lat = row[0]; // Latitude
    const lng = row[1]; // Longitude
    return [
      lat ? convert(lat) : "", // Convert latitude to DMS (column D)
      lng ? convert(lng) : ""  // Convert longitude to DMS (column E)
    ];
  });

The map function is used on each row of the values array to convert latitude and longitude into DMS format:
If the latitude is empty, it returns a blank value (""). Otherwise, it converts it to DMS format.
Latitude in DMS format → Output to column D.
Longitude in DMS format → Output to column E.

Function for Converting to DMS Format

function convert(value) {
  const degrees = Math.floor(value); // Degrees (°)
  const minutes = Math.floor((value - degrees) * 60); // Minutes (′)
  const seconds = ((value - degrees) * 60 - minutes) * 60; // Seconds (″)
  return degrees + "° " + minutes + "′ " + seconds.toFixed(2) + "″";
}

This function converts latitude and longitude from decimal format to Degrees, Minutes, and Seconds (DMS) format:
Math.floor(value): Extracts the degrees (°) by truncating the decimal part.
(value - degrees) * 60: Multiplies the decimal part by 60 to calculate the minutes (′).
minutes' decimal part * 60: Calculates the seconds (″) and rounds it to two decimal places.
Combine the result: Returns a formatted string like 35° 41′ 6.31″.

Writing Results to Columns D and E

  sheet.getRange(2, 4, output.length, 2).setValues(output);

The converted DMS format results are written to column D (4th column) and column E (5th column).
2: Specifies the starting row for the output (row 2).
4: Specifies the starting column for the output (column D).
output.length: Defines the number of rows to write, matching the number of rows in the input data.
2: Specifies the number of columns to write (D and E).