convert to decimal degree from DMS

[student]

Hello,

I have data in DMS (degree minute second) format and I need to convert
to decimal degrees. I would love to use a script that i saw published
on these pages (and which i can no longer find)- but i have never used
macros before... and i got a bit tripped up last time. Does anyone
have the time to post their (accurate) script for this conversion &
instructions for step by step implementation? Much Abliged-

 

[Mark Lincoln]

If your degrees, minutes and seconds are in separate columns, you can
do this:

Assume 121 degrees, 8 minutes, 6 seconds, in A1, B1, C1, respectively.

in D1 enter this formula:

=A1+((B1*60)+C1)/3600

 

[student]

thanks! unfortunately the coordinates are dms in one single column.

anyone else have a suggestion?

 

[Mark Lincoln]

Please give me examples of your data. I'll need to know if the format
of the data is consistent from cell to cell.

 

[Tom Ogilvy]

are you using Time notation to store the value

60:20:10 for example.

if so, you just multiply the cell by 24. Assume A1 holds the DD:MM:SS, then
B1 = A1*24

http://www.cpearson.com/excel/latlong.htm
See Chip Pearson's pag eon this.

 

[student]

thanks! an example of a north latitude coordinate (contained in one
cell) is:

32* 35' 19"

I need to find a dependable routine for converting this type of data to
decimal degrees.

 

[Ron Rosenfeld]

thanks! an example of a north latitude coordinate (contained in one
cell) is:

32* 35' 19"

I need to find a dependable routine for converting this type of data to
decimal degrees.

Assuming your example is consistent, then:

=MID(A1,FIND("'",A1)+2,2)/3600+MID(A1,
FIND(" ",A1)+1,2)/60+LEFT(A1,FIND("*",A1)-1)

If your example is not consistent, we will need to see all the variations.

Consistent means:

1. The "degrees" is terminated by an asterisk
2. The "minutes" follows the first <space> and is terminated by a single
quote, and always contains two digits (e.g. 07' and not 7')
3. The "seconds" follows the <quote><space> and is always two digits.

If the format is not consistent, other solutions can easily be derived.


--ron