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
1	Lat	Lon	DEC Lat	DEC Lon
2	N 34 37.760	W 84 11.565	34.62933333	-84.1928

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =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) D2 =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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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:

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

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

=ROUND(( LEFT($A69,FIND(" ",A69)-1)+RIGHT($A69,LEN($A69)-FIND(" ",$A69)+0)/60 ),6)

=MID(A69,FIND(" ",A69)+1,256)&LEFT(A69,FIND(" ",A69)-2)

Thank you for the help, now my brain wont hurt. Splitting them up was the easy part.l

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[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)," ",":")