Can this be done in Excell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Back
Top