Home / Coordinates and Location Formats

Convert Latitude and Longitude to Decimal Degrees in Excel: Three Working Methods

A workspace with folders and a laptop showing an analytics screen

Photo: Mikhail Nilov / Pexels

Every data cleaning session with location data hits the same wall: the export arrives as degrees, minutes, and seconds, or as text strings with degree symbols, and the mapping tool wants decimal degrees. Converting one pair is a job for any online converter. Converting a column is a job for Excel, and Excel can do it three ways depending on how your data is shaped. This guide covers all three, with the sign rule that causes most of the silent errors. The underlying math, what decimal degrees are and how the formats relate, is in our guide to latitude and longitude formats; this article is the spreadsheet workflow at scale.

Interactive tool

The Excel Conversion: Three Methods in Order

Check off each step as you finish it. Your progress stays on this page.

0 of 6 complete

0% done

The Math in One Line

Decimal degrees are a single number: degrees, plus minutes divided by 60, plus seconds divided by 3600, with a negative sign for south latitudes and west longitudes. Everything below is machinery for applying that one line to hundreds of rows without typing it four hundred times.

Method 1: Split Columns (Degrees, Minutes, Seconds in Separate Cells)

The cleanest case: your export has degrees in column A, minutes in B, seconds in C, and a hemisphere letter in D. In E2, enter:

```

=IF(OR(D2="S",D2="W"),-1,1)*(A2+B2/60+C2/3600)

```

Fill down and the whole column converts. Read the formula in two halves: the second half is the one line of math; the first half is the sign rule, multiplying by minus one when the hemisphere is S or W. If your export has no hemisphere column because the signs are already carried in the degrees column, drop the IF and use =A2+ABS(B2)/60+ABS(C2)/3600 with signed A, or simply =A2+B2/60+C2/3600 when minutes and seconds are unsigned and degrees carries the sign. The failure to avoid: minutes or seconds that arrive already signed, which double counts the negative. Check one row by hand against a known converter before trusting four hundred.

Worked example: 41 degrees, 24 minutes, 12 seconds north becomes 41 + 0.4 + 0.003333 = 41.403333. The same numbers west become -41.403333. If your formula's output for the sample row does not match those figures to the last digit shown, the formula is wrong in a way that will be wrong identically on every row, which is the danger and the mercy of spreadsheets: check row one like a hawk, then fill with confidence.

Method 2: One Text Cell ("41°24'12\"N")

Exports from GPS devices and older software often arrive as a single text string. Modern Excel (Microsoft 365) can parse it with TEXTBEFORE and TEXTAFTER, which extract the numbers between the symbols:

```

=TEXTBEFORE(A2,"°") + TEXTBEFORE(TEXTAFTER(A2,"°"),"'")/60 + TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600

```

Then apply the sign in a second step by testing the last character: multiply by -1 when RIGHT(A2,1) is "S" or "W". Two warnings from experience. First, the seconds mark in real exports is often a typographic quote rather than a straight quote, and TEXTAFTER searching for one will not find the other; use Find and Replace to normalize the symbols in the source column first. Second, some strings use decimal minutes (41°24.2'N) with no seconds at all; the formula above then fails on the missing seconds term, so branch: if the string contains two apostrophes worth of structure use the full parse, otherwise parse degrees and decimal minutes only. Ten minutes spent normalizing the source column saves an afternoon of formula archaeology.

Hands using a calculator and writing in a notebook at a workspace with a laptop
Hands using a calculator and writing in a notebook at a workspace with a laptop

Method 3: A VBA User Defined Function

When you convert files like this every week, a function beats a formula. Press Alt+F11, insert a module, and paste:

```vba

Function DMStoDD(s As String) As Double

Dim t As String, sign As Double

t = Replace(Replace(Replace(s, "°", " "), "'", " "), """", " ")

sign = 1

If InStr(t, "S") > 0 Or InStr(t, "W") > 0 Then sign = -1

t = Replace(Replace(Replace(t, "N", ""), "S", ""), "E", "")

t = Replace(Replace(t, "W", ""), ",", " ")

Dim parts() As String

parts = Split(Application.Trim(t), " ")

DMStoDD = sign * (Val(parts(0)) + Val(parts(1)) / 60 + Val(parts(2)) / 3600)

End Function

```

Now =DMStoDD(A2) converts a text cell in place, tolerant of the symbols because it strips them all to spaces and reads the three numbers in order. Keep the workbook saved as macro enabled (.xlsm), and expect security prompts when the file travels; that friction is the price of the convenience, and it is why Methods 1 and 2 remain the right default for one off jobs and shared files.

The Finish Everyone Forgets: Paste Values

Your converted column currently contains formulas pointing at the source columns. Delete or move the source data and the results break; send the file to a colleague whose layout differs and they break creatively. When the conversion is verified, copy the result column and Paste Special, Values over itself. The numbers become numbers, independent of the machinery that made them. Then round deliberately: use ROUND to five decimal places for phone sourced data (about 1.11 m of resolution, which matches what a phone can honestly measure), or leave full precision for survey sourced data. Rounding at the end, on values, as a conscious act, beats inheriting fifteen decimal places of floating point noise that looks like knowledge and is not. Once the column is clean values, it drops straight into any mapping tool, including the address based workflows in our guide to finding coordinates by address, in reverse.

Checking Your Work

Verify three rows, not one: the first, a middle row, and any row with a southern or western hemisphere, since the sign rule is where conversions die. Cross check against any trustworthy single pair converter. Then scan the full column for impossibilities: latitudes outside -90 to 90, longitudes outside -180 to 180, and zeros where the source had data, each of which names a parse failure with an address. A two minute audit of extremes catches every systematic error this workflow can produce, because spreadsheet errors are never random; they are perfectly consistent, which makes them easy to find and embarrassing to ship.

The Other Shapes Your Data Will Arrive In

Real exports rarely limit themselves to the three shapes above. Four more appear constantly, and each has a one move answer:

  • Degrees and decimal minutes (41° 24.200' N, the marine and aviation habit): the math is degrees + minutes/60, no seconds term. The split column formula becomes =A2+B2/60 with the same sign wrapper, and text parsing stops after the minutes extraction.
  • Hemisphere first (N 41°24'12", W 073°56'30"): the sign test must look at the first character instead of the last, and exports that mix hemisphere first latitudes with hemisphere last longitudes in adjacent columns are why you test the sign on a row of each.
  • Everything in one cell, comma separated ("41.4033, -73.9417" already decimal, or the DMS equivalent): Text to Columns on the comma splits latitude from longitude before any of the methods above apply. Half of all "conversion" jobs end at this step, because the data was only packaged, not formatted, against you.
  • Negative degrees with positive minutes (-41 24 12 meaning south): the naive =A2+B2/60+C2/3600 computes -40.9964, wrong by nearly a full degree, because the minutes must inherit the degrees' sign. The robust form is =SIGN(A2)*(ABS(A2)+B2/60+C2/3600). This single variant accounts for a remarkable share of the world's subtly wrong spreadsheets, because its output looks plausible and lands only tens of kilometers from the truth.

Clean the Source Before You Convert It

Every method in this article gets easier when the source column is normalized first, and the cleaning pass is always the same four sweeps. Replace typographic symbols (curly quotes, the prime and double prime characters ′ and ″) with straight ASCII quotes so text functions can find them. Trim stray spaces, which ride along from PDF copies and web tables and break exact symbol searches. Standardize hemisphere letters to a single case and a single position, first or last, per column. And split latitude and longitude into separate columns before converting either, because a combined cell forces every formula to do two jobs and fail at both. Ten minutes of cleaning, then the conversion itself is the easy part, which is the correct order of difficulty: spreadsheets reward preparation and punish improvisation, at scale, in silence.

The Three Minute Audit Before You Trust the Column

A converted column deserves the same suspicion as any other calculation that arrived silently. Run three checks. Range check: latitudes must fall between -90 and 90 and longitudes between -180 and 180; a value outside those fences is not a coordinate, it is an error message, and filters find them in seconds. Cluster check: sort or glance down the column for the row that moved to another continent, the one whose minutes inherited the wrong sign or whose hemisphere letter was missed; in a clean conversion of one region's data, every row should live in the same neighborhood of the planet. Reverse check: pick one row at random and convert it back by hand with the degrees minutes seconds division in reverse. If the round trip returns your starting value, the formula is right in the way that matters. Three minutes, three checks, and the column graduates from output to data.

Keep the Formula With the Data

A last professional habit: save the workbook with the conversion formulas intact in a methods sheet alongside the pasted values, and note the source format and the date in a cell where a future reader cannot miss it. Spreadsheets outlive their authors' memory, and a column of decimals with no provenance invites the next person to guess which of the three methods, and which sign convention, produced it. The methods sheet is the answer, preserved: the formula, the source shape it consumed, and the audit you ran. Thirty seconds of documentation now is the difference between data that can be trusted and extended, and data that must be converted all over again by someone who trusts nothing they cannot reproduce.

Related Guides

Readers working through this topic usually need its closest companions too. These coordinates guides cover the neighboring ground:

Frequently Asked Questions

What is the Excel formula to convert degrees, minutes, seconds to decimal degrees?

With degrees in A2, minutes in B2, and seconds in C2: =A2+B2/60+C2/3600. Add the hemisphere sign by wrapping it: =IF(OR(D2="S",D2="W"),-1,1)*(A2+B2/60+C2/3600) when the hemisphere letter sits in D2. South latitudes and west longitudes are negative in decimal degrees.

How do I convert a text coordinate like 41°24'12"N in one cell?

In Microsoft 365 Excel, extract the three numbers with TEXTBEFORE and TEXTAFTER around the degree, minute, and second symbols, combine them as degrees + minutes/60 + seconds/3600, then multiply by -1 if the string ends in S or W. Normalize typographic quotes to straight quotes first with Find and Replace, or the extraction will miss. For repeated work, the VBA function in this article is more forgiving.

Why did my converted coordinates land in the ocean or the wrong continent?

Almost always the sign. A missing negative on a west longitude mirrors the point across the Atlantic; a missing negative on a south latitude mirrors it across the equator. Check the hemisphere handling on one known row, confirm south and west come out negative, and recheck any row whose source used S or W before blaming the formula's arithmetic.

How many decimal places should I keep after converting?

Match the source. Five decimal places (about 1.11 m) is the honest ceiling for phone collected data. Survey grade sources justify more. Use ROUND on the finished values rather than formatting the display, because display formatting hides digits that other software will still read.

Why should I paste values after converting?

Because a formula column depends on the source columns staying exactly where they are. Pasting values over the formulas freezes the results as plain numbers that survive deleting the source data, rearranging the sheet, or sending the file to someone else. It is the difference between a conversion and a screenshot of one.

Photo credits: Mikhail Nilov (Pexels); Mikhail Nilov (Pexels). Photos are served from this site; follow the credit to the library page for the original.