Convert Degree Minutes to decimal degrees

excel gps coordinates degrees garmin
user2114562 picture user2114562 · Oct 3, 2013 · Viewed 27.7k times · Source

I have location points collected from a garmin device stored in an excel sheet in Degree Minutes format --- W00208.172,N1046.977 How can I convert it to either Decimal Degrees or Degrees Minutes seconds Format ?

Answer

L33tCh picture L33tCh · Apr 21, 2016

So I was looking for a lazy answer and wasn't happy so gonna put here what I made in the end:

Wanting to convert between these two formats:

33°59'05.5"S 22°39'42.7"E and -33.98485,22.66186

The left being "Degrees Minutes Seconds" and the right being "Decimal"

Of course I have to make some assumptions so they are that it assumes your are following the above formats exactly.

Ok so first is from Degree to Decimal (the source being in cell B2):

=ROUNDDOWN(if(mid(B2,find(" ",B2)-1,1)="S","-","")&mid(B2,1,find("°",B2)-1)+mid(B2,find("°",B2)+1,find("'",B2)-find("°",B2)-1)/60+mid(B2,find("'",B2)+1,find("""",B2)-find("'",B2)-1)/60/60,5)&","&rounddown(if(right(B2,1)="W","-","")&mid(right(B2,find(" ",B2)-1),1,find("°",right(B2,find(" ",B2)-1))-1)+mid(right(B2,find(" ",B2)-1),find("°",right(B2,find(" ",B2)-1))+1,find("'",right(B2,find(" ",B2)-1))-find("°",right(B2,find(" ",B2)-1))-1)/60+mid(right(B2,find(" ",B2)-1),find("'",right(B2,find(" ",B2)-1))+1,find("""",right(B2,find(" ",B2)-1))-find("'",right(B2,find(" ",B2)-1))-1)/60/60,5)

And then from Decimal to Degree (the source being in cell C2):

=abs(ROUNDDOWN(left(C2,find(",",C2)-1))) & "°" & ROUNDDOWN((abs(left(C2,find(",",C2)-1))-abs(ROUNDDOWN(left(C2,find(",",C2)-1))))*60) & "'" & round(((abs(left(C2,find(",",C2)-1))-abs(ROUNDDOWN(left(C2,find(",",C2)-1))))*60- rounddown((abs(left(C2,find(",",C2)-1))-abs(ROUNDDOWN(left(C2,find(",",C2)-1))))*60))*60,1) & """"&if(value(left(C2,find(",",C2)-1))<0,"S","N")& " " & abs(ROUNDDOWN(RIGHT(C2,len(C2)-find(",",C2)))) & "°" & rounddown((abs(RIGHT(C2,len(C2)-find(",",C2)))-abs(ROUNDDOWN(RIGHT(C2,len(C2)-find(",",C2)))))*60) & "'" & round(((abs(RIGHT(C2,len(C2)-find(",",C2)))-abs(ROUNDDOWN(RIGHT(C2,len(C2)-find(",",C2)))))*60- rounddown((abs(RIGHT(C2,len(C2)-find(",",C2)))-abs(ROUNDDOWN(RIGHT(C2,len(C2)-find(",",C2)))))*60))*60,1) & """"&if(value(right(C2,len(C2)-find(",",C2)))<0,"W","E")

If only you could have variables in formulae... most of that is duplicate formulae with some simple enough rules...Anyway, hope it helps someone.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More