Converting coordinates in Microsoft Excel typically involves translating between Degrees, Minutes, Seconds (DMS) (e.g., 34° 44’ 45” N) and Decimal Degrees (DD) (e.g., 34.7459). Excel does not have a built-in “coordinate” data type, but you can effortlessly manage conversions using standard mathematical formulas. Here is how to perform both conversion types step-by-step. Convert DMS to Decimal Degrees (DD)
To map coordinates or use them in GIS software, you will need them in Decimal Degrees. The mathematical logic is simple: Decimal Degrees = Degrees + (Minutes / 60) + (Seconds / 3600). Option A: If your data is already split into 3 columns
If your Degrees, Minutes, and Seconds are already stored in separate cells (e.g., Column A, B, and C): Click on an empty cell (e.g., D2) for your result. Enter the formula: =A2 + (B2 / 60) + (C2 / 3600).
For Southern or Western hemispheres, multiply the final result by -1 (or subtract the values from 0) to make them negative. Option B: If the data is in one single string If your cell looks like 34°44’45”N: Select your column of coordinates.
Go to the Data tab on the Excel ribbon and click Text to Columns.
Choose Delimited and use the symbols (°, ‘, “) as delimiters to split the data across three separate columns.
Apply the math formula from Option A to the newly separated columns. Convert Decimal Degrees (DD) to DMS
Converting back to DMS requires breaking down the decimal remainder into chunks of 60. Excel Formula Example (Assuming DD is in cell A2) Degrees =TRUNC(A2) Grabs the whole number part. Minutes =TRUNC((ABS(A2) - ABS(TRUNC(A2)))60) Multiplies the remaining decimal by 60. Seconds
=((ABS(A2) - ABS(TRUNC(A2))) * 60 - TRUNC((ABS(A2) - ABS(TRUNC(A2))) * 60)) * 60 Multiplies the remaining minutes decimal by 60. Combine Them Into a Single Format
To stitch these values back into a classic looking DMS coordinate string, you can concatenate the outputs and add the symbol formatting:
=TRUNC(A2) & “° ” & TRUNC((ABS(A2)-ABS(TRUNC(A2)))*60) & “’ ” & ROUND(((ABS(A2)-ABS(TRUNC(A2)))*60-TRUNC((ABS(A2)-ABS(TRUNC(A2)))*60))*60, 2) & “”“” Use code with caution.
(Note: The four quotation marks ”“”” at the end are required by Excel to output a single literal double-quote character representing seconds). Pro-Tips for Coordinate Management
Leave a Reply