Converting DM to DD

[campingjunkie]

I have been beating away at this keybod for hours and i am stuck trying to convert my gps coordinates to another format. Can someone help with this?

here is what I am trying to do:


Cell A Cell B

N 34 37.760,W 84 11.565 -----> 34.629333, -84.19275


or


Cell A Cell B Cell C
N 34 37.760 W 84 11.565 34.629333, -84.19275


Any Help would be appreciated. Thank you.

 

[CalcSux78]

Welcome to the forum!

Breaking down your coordinates...
N 34 would be your "Hours" portion of the coordinates.
37.760 would be your "Minutes portion.

To convert, add your hours portion plus Minutes divided by 60

34 + (37.760/60) = 34.629333

https://www.excel-exercise.com/convert-latitude-longitude/

 

[campingjunkie]

Thank you,
I under stand that part, i gues i left out that I"m looking for a formula that would convert. I have been hacking on this:

=MID(A66, FIND(" ", A66, FIND(" ", A66)+1)+1,256)/60

but this only get me from

N 40 32.034 to 0.5339 Now how do i get the 40 part back in to the formula? with a left?

i figured out how to go from 40 32.034 to 40.5339 with out the N. as soon and I add the N, i get lost. Im using:

=LEFT(A63,FIND(" ",A63)-1)+RIGHT(A63,LEN(A63)-FIND(" ",A63)+0)/60

to get: 40 32.034 to 40.5339.

 

[CalcSux78]

A few questions:
How many records are you converting? (dozens, hundreds, thousands...)

Is the data all entered by hand or generated?

Are you preferring a worksheet formula or are you comfortable with scripting?

 

[campingjunkie]

converting close to about 5 thousand locations.

it was all put in by hand.

I prefer a worksheet formula, i have no clue on scripting,

I have been able to figure out all the other styles of conversions in formulas, this will be the last variation that i need. i can get the strings i need one at a time, but when i goto combine two strings it gets all messy. I cant figure out how put the stings in the correct order with a + or a & or put the () in the right spot.

if it help the N and W are in all the cells.

 

[CalcSux78]

This would work if your Latitude and Longitude values are in separate columns:
Excel 2010

	A	B	C	D
Lat	Lon	DEC Lat	DEC Lon
N 34 37.760	W 84 11.565

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

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

[TD="align: right"]34.62933333[/TD]
[TD="align: right"]-84.1928[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(OR(LEFT(A2,1)="W",LEFT(A2,1)="S"),-1,1)*(MID(A2,(FIND(" ",A2,1)+1),FIND(" ",A2,(FIND(" ",A2,1)+1))-(FIND(" ",A2,1)+1))+RIGHT(A2,LEN(A2)-FIND(" ",A2,(FIND(" ",A2,1)+1)))/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(OR(LEFT(B2,1)="W",LEFT(B2,1)="S"),-1,1)*(MID(B2,(FIND(" ",B2,1)+1),FIND(" ",B2,(FIND(" ",B2,1)+1))-(FIND(" ",B2,1)+1))+RIGHT(B2,LEN(B2)-FIND(" ",B2,(FIND(" ",B2,1)+1)))/60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[campingjunkie]

Thanks, They work perfectly. I was close. just not close enough or how long it would have taken me to get there.

this is how far i got:

[TABLE="width: 1194"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 7"]=LEFT(A63,FIND(" ",A63)-1)+RIGHT(A63,LEN(A63)-FIND(" ",A63)+0)/60[/TD]
[/TR]
[TR]
[TD="colspan: 7"]=LEFT(A69,FIND(" ",A69)-1)+RIGHT(A69,LEN(A69)-FIND(" ",A69)+0)/60[/TD]
[/TR]
[TR]
[TD="colspan: 7"]=ROUND(( LEFT($A69,FIND(" ",A69)-1)+RIGHT($A69,LEN($A69)-FIND(" ",$A69)+0)/60 ),6)[/TD]
[/TR]
[TR]
[TD="colspan: 7"]=MID(A69,FIND(" ",A69)+1,256)&LEFT(A69,FIND(" ",A69)-2)[/TD]
[/TR]
[TR]
[TD="colspan: 7"]Thank you for the help, now my brain wont hurt. Splitting them up was the easy part.l[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Late to the party, but here is a shorter formula to try:

=1440*REPLACE(SUBSTITUTE(TRIM(A2)," ",":"),1,1,0)*IF(OR(LEFT(TRIM(A2))={"S","W"}),-1,1)

 

[Rick Rothstein]

Even later to the party, but with what is probably the shortest formula...

=1440*SUBSTITUTE(MID(A2,3,99)," ",":")