{"id":17234,"date":"2025-01-21T19:00:07","date_gmt":"2025-01-21T10:00:07","guid":{"rendered":"https:\/\/84office.jp\/?p=17234"},"modified":"2025-01-21T19:00:08","modified_gmt":"2025-01-21T10:00:08","slug":"googlesheet-latitude-longitude-conversion","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/googlesheet-latitude-longitude-conversion\/","title":{"rendered":"3 Easy Ways to Convert Coordinates to DMS in Google Sheets"},"content":{"rendered":"
<\/div>\n

Have you ever found decimal format coordinates (e.g., 35.685085) hard to interpret?<\/p>\n\n\n\n

Converting them into Degrees, Minutes, and Seconds (DMS) format (e.g., 35\u00b0 41\u2032 6.31\u2033) can make the data much easier to read and simplify tasks like mapping or verifying location information.<\/p>\n\n\n\n

In this article, we\u2019ll explore 3 methods to convert latitude and longitude into DMS format<\/strong> using Google Sheets:<\/p>\n\n\n\n

    \n
  1. Using formulas<\/strong><\/li>\n\n\n\n
  2. Creating a custom function<\/strong><\/li>\n\n\n\n
  3. Utilizing Google Apps Script (GAS)<\/strong><\/li>\n<\/ol>\n\n\n\n

    Choose the method that best suits your needs and data size!<\/p>\n\n\n

    \t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\u3042\u308f\u305b\u3066\u8aad\u307f\u305f\u3044<\/span>\n\t\t\t\t\t
    \"\"<\/figure><\/div>\t\t\t\t\t
    \n\t\t\t\t\t\tHow to Automatically Convert Addresses to Latitude and Longitude in Google Sheets Using Google Ma…<\/a>\n\t\t\t\t\t\tIn the previous article, we introduced how to obtain an API key for using the Google Maps API. In this article, we will show you how to use the Google Maps A…<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\n

    Comparison of Methods to Convert Latitude and Longitude to DMS Format<\/strong><\/h2>\n\n\n\n
    Method<\/th>Features<\/strong><\/th>Best Use Cases<\/strong><\/th><\/tr><\/thead>
    Formulas<\/strong><\/td>Simple to use<\/span>
    Requires input for each cell manually<\/span><\/td>    		Ideal for quickly converting small datasets<\/span>
    Suitable if you prefer not to use scripts<\/span><\/td><\/tr>
    Custom Functions<\/strong><\/td>Easy to implement with a simple script<\/span>
    Requires input for each cell<\/span><\/td>    		Useful for repetitive tasks across multiple sheets<\/span><\/td><\/tr>
    Scripts<\/strong><\/td>Capable of processing large datasets in one go<\/span>
    Fully automated<\/span><\/td>    		Efficient when dealing with large datasets<\/span>
    Ideal for automating regular processing<\/span><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n
    What is DMS Format?<\/strong><\/span><\/i><\/i><\/span><\/summary>
    \n

    DMS (Degrees, Minutes, Seconds) is a format used to represent latitude and longitude as follows:<\/p>\n\n\n\n

      \n
    • Degrees (\u00b0):<\/strong> The integer part of the decimal value.<\/li>\n\n\n\n
    • Minutes (\u2032):<\/strong> The decimal part of the degrees multiplied by 60, taking only the integer part.<\/li>\n\n\n\n
    • Seconds (\u2033):<\/strong> The decimal part of the minutes multiplied by 60.<\/li>\n<\/ul>\n\n\n\n

      \u4f8b<\/strong>:<\/p>\n\n\n\n

      Decimal Format:<\/strong> 35.685085<\/p>\n\n\n\n

      DMS Format:<\/strong> 35\u00b0 41\u2032 6.31\u2033<\/p>\n<\/div><\/details>\n<\/div>\n\n\n\n

      Method 1: Converting to DMS Format Using Formulas<\/strong><\/h2>\n\n\n\n
      \"\"<\/figure>\n\n\n\n

      You can easily convert latitude and longitude to DMS format using Google Sheets formulas.<\/p>\n\n\n\n

      Formulas<\/strong><\/h4>\n\n\n\n

      For Latitude<\/strong> (e.g., cell B2):<\/p>\n\n\n\n

      <\/path><\/path><\/svg><\/span>
      =<\/span>INT<\/span>(B2) <\/span>&<\/span> <\/span>"\u00b0 "<\/span> <\/span>&<\/span> <\/span>INT<\/span>((B2<\/span>-<\/span>INT<\/span>(B2))<\/span>*<\/span>60<\/span>) <\/span>&<\/span> <\/span>"\u2032 "<\/span> <\/span>&<\/span> <\/span>ROUND<\/span>(((B2<\/span>-<\/span>INT<\/span>(B2))<\/span>*<\/span>60<\/span>-<\/span>INT<\/span>((B2<\/span>-<\/span>INT<\/span>(B2))<\/span>*<\/span>60<\/span>))<\/span>*<\/span>60<\/span>, <\/span>2<\/span>) <\/span>&<\/span> <\/span>"\u2033"<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      For Longitude<\/strong> (e.g., cell C2):<\/p>\n\n\n\n
      <\/path><\/path><\/svg><\/span>
      =<\/span>INT<\/span>(C2) <\/span>&<\/span> <\/span>"\u00b0 "<\/span> <\/span>&<\/span> <\/span>INT<\/span>((C2<\/span>-<\/span>INT<\/span>(C2))<\/span>*<\/span>60<\/span>) <\/span>&<\/span> <\/span>"\u2032 "<\/span> <\/span>&<\/span> <\/span>ROUND<\/span>(((C2<\/span>-<\/span>INT<\/span>(C2))<\/span>*<\/span>60<\/span>-<\/span>INT<\/span>((C2<\/span>-<\/span>INT<\/span>(C2))<\/span>*<\/span>60<\/span>))<\/span>*<\/span>60<\/span>, <\/span>2<\/span>) <\/span>&<\/span> <\/span>"\u2033"<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
      \n
      Explanation of the Formula<\/strong><\/span><\/i><\/i><\/span><\/summary>
      \n
      INT<\/code>Function:Extracts the integer part of the number by discarding the decimal part.<\/p>\n\n\n\n
        \n
      • INT(B2)<\/code>: Retrieves the degrees (\u00b0)<\/strong>.<\/li>\n\n\n\n
      • (B2-INT(B2))*60<\/code>: Calculates the minutes (\u2032)<\/strong> by taking the decimal part of the degree (B2 – INT(B2)) and multiplying it by 60.<\/li>\n\n\n\n
      • ((B2-INT(B2))*60-INT(...))*60<\/code>: Computes the seconds (\u2033)<\/strong> by taking the decimal part of the minutes and multiplying it by 60.
        The ROUND<\/code> function is used to round the result to 2 decimal places<\/strong> for better readability.<\/li>\n<\/ul>\n<\/div><\/details>\n<\/div>\n\n\n\n
        Method 2: Converting to DMS Format Using a Custom Function<\/strong><\/h2>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        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.<\/p>\n\n\n\n
        Custom Function Formulas<\/strong><\/h4>\n\n\n\n
        For Latitude<\/strong> (e.g., cell B2):<\/p>\n\n\n\n
        <\/path><\/path><\/svg><\/span>
        =<\/span>DMS<\/span>(B2)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        For Longitude<\/strong> (e.g., cell C2):<\/p>\n\n\n\n
        <\/path><\/path><\/svg><\/span>
        =<\/span>DMS<\/span>(C2)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        Steps to Set Up the Custom Function<\/strong><\/h4>\n\n\n\n
        To use a custom function, follow these steps:<\/p>\n\n\n\n
        \n
        STEP<\/span><\/div>
        Open Apps Script Editor<\/div>
        \n
        \"\"<\/figure>\n<\/div>\n\n\n
        Open a Google Spreadsheet and click on \u201cExtensions<\/strong>\u201d in the menu, then \u201cApps Script<\/strong>\u201d to open the Apps Script editor.<\/p>\n<\/div><\/div>\n\n\n\n
        STEP<\/span><\/div>
        Create the Script<\/strong><\/div>
        \n
        \"\"<\/figure>\n<\/div>\n\n\n
        Since the default function myFunction() {}<\/code> is present, delete it and replace it with the script below.<\/p>\n\n\n\n
        <\/path><\/path><\/svg><\/span>
        function<\/span> <\/span>DMS<\/span>(<\/span>value<\/span>) {<\/span><\/span>\n  <\/span>const<\/span> degrees <\/span>=<\/span> Math.<\/span>floor<\/span>(value); <\/span>\/\/ Extract the degrees (\u00b0)<\/span><\/span>\n  <\/span>const<\/span> minutes <\/span>=<\/span> Math.<\/span>floor<\/span>((value <\/span>-<\/span> degrees) <\/span>*<\/span> <\/span>60<\/span>); <\/span>\/\/ Calculate the minutes (\u2032)<\/span><\/span>\n  <\/span>const<\/span> seconds <\/span>=<\/span> ((value <\/span>-<\/span> degrees) <\/span>*<\/span> <\/span>60<\/span> <\/span>-<\/span> minutes) <\/span>*<\/span> <\/span>60<\/span>; <\/span>\/\/ Calculate the seconds (\u2033)<\/span><\/span>\n  <\/span>return<\/span> degrees <\/span>+<\/span> <\/span>"\u00b0 "<\/span> <\/span>+<\/span> minutes <\/span>+<\/span> <\/span>"\u2032 "<\/span> <\/span>+<\/span> seconds.<\/span>toFixed<\/span>(<\/span>2<\/span>) <\/span>+<\/span> <\/span>"\u2033"<\/span>; <\/span>\/\/ Format the result as DMS<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
        \n
        Script Description<\/span><\/i><\/i><\/span><\/summary>
        \n
        \n
        Math.floor(value)<\/code><\/dt>\n\n\n\n
        \n
        Retrieves the degrees (\u00b0) by truncating the decimal part.<\/p>\n<\/dd>\n\n\n\n
        (value - degrees) * 60<\/code><\/dt>\n\n\n\n
        \n
        Multiplies the decimal part by 60 to calculate the minutes (\u2032).<\/p>\n<\/dd>\n\n\n\n
        (minutes\u306e\u5c0f\u6570\u90e8\u5206 * 60)<\/code><\/dt>\n\n\n\n
        \n
        Calculates the seconds (\u2033) and rounds it to two decimal places.<\/p>\n<\/dd>\n<\/dl>\n<\/div><\/details>\n<\/div>\n<\/div><\/div>\n\n\n\n
        STEP<\/span><\/div>
        Save the Script<\/strong><\/div>
        \n
        \"\"<\/figure>\n<\/div>\n\n\n
        After writing the script, give it a name and save it.<\/p>\n\n\n\n
        Example: “Coordinates (DMS Format)”<\/p>\n<\/div><\/div>\n\n\n\n
        STEP<\/span><\/div>
        Run the Script<\/div>
        \n
        \"\"<\/figure>\n\n\n\n
        Run the script once to authorize the required permissions.<\/p>\n\n\n\n
        \n
        If you’re running the script for the first time, you need to authorize it.<\/p>\n\n\n\n
        Therefore, press “Review Permissions.<\/strong>“<\/strong><\/p>\n\n\n
        \n
        \"\"<\/figure>\n<\/div>\n\n\n
        \n
        Detailed Authorization Steps<\/span><\/i><\/i><\/span><\/summary>
        \n
        Press “Advanced.”<\/strong><\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n\n\n\n\n
        Press “Go to Untitled project (Unsafe).”<\/strong><\/p>\n\n\n
        \n
        \"\"<\/figure>\n<\/div>\n\n\n
        After that, press “Allow.”<\/strong><\/p>\n\n\n
        \n
        \"\"<\/figure>\n<\/div>\n\n\n\n<\/div><\/details>\n<\/div>\n<\/div><\/div>\n<\/div><\/div>\n<\/div>\n\n\n\n
        Once the setup is complete, enter the custom function in a cell to use it.<\/p>\n\n\n\n
        Method 3: Bulk Conversion with Google Apps Script<\/strong><\/strong><\/h2>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        If you need to convert a large amount of data at once, using a script is the most efficient approach.<\/p>\n\n\n\n
        The following script converts latitude (column B) and longitude (column C) into DMS format and outputs the results in columns D and E, respectively.<\/p>\n\n\n\n
        Key Features:<\/strong><\/p>\n\n\n\n