Convert Degree Min Sec to Decimal Degree Lat/Longitude

R

rodwilma

New Member
Joined
Nov 12, 2012
Messages
11
I have a spreadsheet with coordinates in the following format and I need to convert these to decimal degrees. Anyone know how?

[TABLE="width: 282"]
<TBODY>[TR]
[TD]Latitude </SPAN></SPAN>
[/TD]
[TD]Longitude </SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-28.8</SPAN></SPAN>
[/TD]
[TD]W 081-11-57.2</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-28.6</SPAN></SPAN>
[/TD]
[TD]W 081-11-57.5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-06.5</SPAN></SPAN>
[/TD]
[TD]W 081-14-06.7</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-05.0</SPAN></SPAN>
[/TD]
[TD]W 081-14-05.6</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-04.7</SPAN></SPAN>
[/TD]
[TD]W 081-14-02.1</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]N 29-36-04.5</SPAN></SPAN>
[/TD]
[TD]W 081-14-09.6</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Any input is greatly appreciated.

Thanks!
 
Sort by date Sort by votes
Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

Hi Excel experts,
I had a go at trying to learn from your formatting lessons above here for my coordinate conversion project, but failed miserably for a number of reasons - one of many being inconsistent input length of digits and decimal places.

(It's a little private project converting coordinates of UNESCO world heritage sites so I can make a custom map.)

Anyway, would someone be so kind as to please help with coordinates in this format?

Many thanks!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 230px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Desired[/TD]
[TD]Desired[/TD]
[/TR]
[TR]
[TD]Original format (in one cell)[/TD]
[TD]Longitude[/TD]
[TD]Latitude[/TD]
[/TR]
[TR]
[TD]N34 23 47.6 E64 30 57.8[/TD]
[TD="align: right"]34.396556[/TD]
[TD="align: right"]64.516056[/TD]
[/TR]
[TR]
[TD]N34 50 49.00 E67 49 30.90[/TD]
[TD="align: right"]34.846944[/TD]
[TD="align: right"]67.82525[/TD]
[/TR]
[TR]
[TD]S50 0 0 W73 14 57.984[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]-73.24944[/TD]
[/TR]
[TR]
[TD]S27 15 0.00 W55 31 0.00[/TD]
[TD="align: right"]-27.25[/TD]
[TD="align: right"]-55.516667[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

(Oh, except of course that I switched the Longitude for Latitude column names accidentally.)
 
Upvote 0
Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

awembridge said:
Hi Excel experts,
I had a go at trying to learn from your formatting lessons above here for my coordinate conversion project, but failed miserably for a number of reasons - one of many being inconsistent input length of digits and decimal places.

(It's a little private project converting coordinates of UNESCO world heritage sites so I can make a custom map.)

Anyway, would someone be so kind as to please help with coordinates in this format?

Many thanks!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]Desired[/TD]
[TD]Desired[/TD]
[/TR]
[TR]
[TD]Original format (in one cell)[/TD]
[TD]Longitude[/TD]
[TD]Latitude[/TD]
[/TR]
[TR]
[TD]N34 23 47.6 E64 30 57.8[/TD]
[TD="align: right"]34.396556[/TD]
[TD="align: right"]64.516056[/TD]
[/TR]
[TR]
[TD]N34 50 49.00 E67 49 30.90[/TD]
[TD="align: right"]34.846944[/TD]
[TD="align: right"]67.82525[/TD]
[/TR]
[TR]
[TD]S50 0 0 W73 14 57.984[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]-73.24944[/TD]
[/TR]
[TR]
[TD]S27 15 0.00 W55 31 0.00[/TD]
[TD="align: right"]-27.25[/TD]
[TD="align: right"]-55.516667[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I'm sure it's possible to make a one-size-fits-all formula, but it may be easier to just break it down in multiple columns and then convert.

<br />
Book1
ABCDEFGHIJK
1Original format (in one cell)LatitudeLongitude
2N-SDegMinSecDeg DecimalE-WDegMinSecDeg Decimal
3N34 23 47.6 E64 30 57.8N342347.634.39655556E643057.864.5160556
4N34 50 49.00 E67 49 30.90N34504934.84694444E674930.967.82525
5S50 0 0 W73 14 57.984S5000-50W731457.984-73.24944
6S27 15 0.00 W55 31 0.00S27150-27.25W55310-55.516667
Sheet1
Cell Formulas
RangeFormula
B3=LEFT(A3,1)
C3=TRIM(MID(A3,2,2))*1
D3=TRIM(MID(A3,LEN(C3)+3,2))*1
E3=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),3)," ",REPT(" ",50),2),50,50))*1
F3=IF(B3="N",1,-1)*(C3+("0:"&D3&":"&E3)*24)
G3=IF(ISNUMBER(FIND("E",A3)),"E","W")
H3=MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),4)," ",REPT(" ",50),3),50,60)),2,2)*1
I3=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),5)," ",REPT(" ",50),4),50,50))*1
J3=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),6)," ",REPT(" ",50),5),50,50))*1
K3=IF(G3="E",1,-1)*(H3+("0:"&I3&":"&J3)*24)
 
Last edited:
Upvote 0
Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

Alphafrog, many thanks indeed for such thoughtful input. This has made my little project much easier.

Best regards from Munich
 
Upvote 0
Re: How to Convert Degree Min Sec to Decimal Degree Lat/Longitude

AlphaFrog said:
I'm sure it's possible to make a one-size-fits-all formula, but it may be easier to just break it down in multiple columns and then convert.


ABCDEFGHIJK
Original format (in one cell)LatitudeLongitude
N34 23 47.6 E64 30 57.8
N34 50 49.00 E67 49 30.90
S50 0 0 W73 14 57.984
S27 15 0.00 W55 31 0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]N-S[/TD]
[TD="align: center"]Deg[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]Sec[/TD]
[TD="align: center"]Deg Decimal[/TD]
[TD="align: center"]E-W[/TD]
[TD="align: center"]Deg[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]Sec[/TD]
[TD="align: center"]Deg Decimal[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]N[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]47.6[/TD]
[TD="align: center"]34.39655556[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]57.8[/TD]
[TD="align: center"]64.5160556[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]N[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]34.84694444[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]30.9[/TD]
[TD="align: center"]67.82525[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]S[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-50[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]73[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]57.984[/TD]
[TD="align: center"]-73.24944[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]S[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-27.25[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-55.516667[/TD]

</tbody>


Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B3[/TH]
[TD="align: left"]=LEFT( A3,1 )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C3[/TH]
[TD="align: left"]=TRIM( MID(A3,2,2) )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]D3[/TH]
[TD="align: left"]=TRIM( MID(A3,LEN(C3)+3,2) )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]E3[/TH]
[TD="align: left"]=TRIM( MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),3)," ",REPT(" ",50),2),50,50) )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]F3[/TH]
[TD="align: left"]=IF( B3="N",1,-1 )*( C3+("0:"&D3&":"&E3)*24 )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G3[/TH]
[TD="align: left"]=IF( ISNUMBER(FIND("E",A3)),"E","W" )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]H3[/TH]
[TD="align: left"]=MID( TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),4)," ",REPT(" ",50),3),50,60)),2,2 )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]I3[/TH]
[TD="align: left"]=TRIM( MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),5)," ",REPT(" ",50),4),50,50) )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]J3[/TH]
[TD="align: left"]=TRIM( MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),6)," ",REPT(" ",50),5),50,50) )*1[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]K3[/TH]
[TD="align: left"]=IF( G3="E",1,-1 )*( H3+("0:"&I3&":"&J3)*24 )[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Ooh, just one tiny thing for anyone else who may want to use these formulae - it seems to work a little better if you slightly modify the formula in H3 to recall 3 values, just in case your input coordinate is something like S25 13 0 E153 7 60,
otherwise it only picks up "15" instead of "153".


[TABLE="width: 705"]
<tbody>[TR]
[TH]H3[/TH]
[TD]=MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3," ",REPT(" ",50),4)," ",REPT(" ",50),3),50,60)),2,3)*1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
D
  • Question Question
Convert Geo Coordinates to Decimal
Replies
0
Views
624
DJFANDANGO
D
Z
  • Question Question
Convert Longitude & Latitude Start and End Coordinates into Nautical Miles (no API)
Replies
6
Views
2K
zgoldflo
Z
T
  • Question Question
Excel 2016 Hyperlink Concatenate with a growing formula.
Replies
0
Views
1K
ToninoC
T
M
  • Question Question
specific decimal degrees conversion to deg/min/sec values
Replies
8
Views
698
FormR
FormR

Forum statistics

Threads
1,224,812
Messages
6,181,085
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top