I have two columns with the following format:
Long Lat
97-31-10.2 W 25-57-51.2 N

So i need to change this format(Minutes-Degee-Sec) to Decimal Degrees.

So from the first column Long:
(97/3600)+(31/60)+10.2=97.5195.This is the number in Decimal degree for Long.

Can this be automated in Excell ?

Regards
Rick

I hope there is a better way but I think this should work. Select the
two columns. Go to the 'data' tab and then "text to columns". By
selecting delimiters you should convert your data from two columns to 8
columns. Then for the 9th column you enter the formula
=A1+B1/60+C1/3600. You can add the "W" if you want which should be D1.
You do similarly with Latitude so the 10th column is =E1+F1/60+G1/3600
and again you can add the N if you need to.

Note: I think you have your formula wrong....it is degrees, minutes,
seconds so the degrees are correct as is and you divide the seconds by
3600 not the other way around.


--
camjohnson
------------------------------------------------------------------------
camjohnson's Profile: http://www.excelforum.com/member.php...o&userid=33751
View this thread: http://www.excelforum.com/showthread...hreadid=535280

The minutes can be extracted with

=LEFT(A1,FIND("-",A1)-1)

The Degrees can be extracted with


=MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))))))-1)

but looking at the Seconds I would guess that camjohnson's 'helper'
columns would be the easy way without VB code, unles, of course,
someone has a better extraction.

HTH

Jp Wrote:
I have two columns with the following format:
Long Lat
97-31-10.2 W 25-57-51.2 N

So i need to change this format(Minutes-Degee-Sec) to Decimal Degrees.

So from the first column Long:
(97/3600)+(31/60)+10.2=97.5195.This is the number in Decimal degree for
Long.

Can this be automated in Excell ?

Regards
Rick


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535280

After a nap, the formula looks easier, The minutes can be extracted
with

=LEFT(A1,FIND("-",A1)-1)

The Degrees can be extracted with


=MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))))))-1)

The seconds can be extracted with

=MID(A1,FIND("-",MID(A1,FIND("-",A1)+1,99))+FIND("-",A1)+1,FIND("
",A1)-FIND("-",MID(A1,FIND("-",A1)+1,99))-FIND("-",A1)-1)

and the direction can be extracted with

=MID(A1,FIND(" ",A1)+1,1)

So your calculation would be:

=LEFT(A1,FIND("-",A1)-1)/3600+
MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))))))-1)/60
+MID(A1,FIND("-",MID(A1,FIND("-",A1)+1,99))+FIND("-",A1)+1,FIND("
",A1)-FIND("-",MID(A1,FIND("-",A1)+1,99))-FIND("-",A1)-1)

to give 10.74361

or:

=LEFT(A1,FIND("-",A1)-1)+
MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))))))-1)/60
+MID(A1,FIND("-",MID(A1,FIND("-",A1)+1,99))+FIND("-",A1)+1,FIND("
",A1)-FIND("-",MID(A1,FIND("-",A1)+1,99))-FIND("-",A1)-1)/3600

to give 97.5195

Cheers

Bryan Hessey Wrote:
The minutes can be extracted with

=LEFT(A1,FIND("-",A1)-1)

The Degrees can be extracted with


=MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))))))-1)

but looking at the Seconds I would guess that camjohnson's 'helper'
columns would be the easy way without VB code, unles, of course,
someone has a better extraction.

HTH


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535280