Convert decimal to degrees, minutes, seconds

Hello

Is it what you want:



The formula used in final is:

=INT(Al)&"°"&INT(MOD(A,1) 60)&"'"&ROUND(MOD(MOD(Al,1)*60,1)60,0)

Yvan KOENIG (from FRANCE jeudi 13 mars 2008 10:13:1)

Hello

(*
Save the script with the name deci2degrees as an Application or an Application Bundle

If you want to use it with Numbers, put a copy of it in the folder
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

To use it,
Select the group of cells to convert
Copy to the clipboard
Select the destination cell
Goto Scripts > Numbers > deci2degrees
The converted values will be stored in the clipboard then pasted at the cursor.

*)
--(SCRIPT deci2degrees.app]
property paralist : {}
property liste1 : {}

on run
try
set txtDatas to the clipboard
set my paralist to every paragraph of txtDatas
set srcNbRows to count of my paralist
if txtDatas contains tab then
my nettoie()
repeat with p in my paralist
set AppleScript's text item delimiters to tab
set l to every text item of (p as text)
set AppleScript's text item delimiters to ""
copy l to end of my liste1
end repeat
set srcNbCols to count of l
repeat with i from 1 to srcNbCols
repeat with j from 1 to srcNbRows
set iij to item i of item j of my liste1
if iij is not "" then set item i of item j of my liste1 to my deci2degrees(iij)
end repeat
set item j of paralist to my recolle(item j of liste1, tab)
end repeat
set txtDatas to my recolle(paralist, return)
my nettoie()
else
repeat with j from 1 to srcNbRows
set ij to item j of my paralist
if ij is not "" then set item j of my paralist to my deci2degrees(ij)
set txtDatas to my recolle(paralist, return)
end repeat
end if
set the clipboard to txtDatas
set my paralist to {}

tell application "Numbers" to activate
tell application "System Events" to tell process (my whichProcess("Numbers")) to keystroke "v" using {command down}

on error
my nettoie()
set my paralist to {}
end try
end run

--=============

on deci2degrees(n)
local d, ms, m
set d to n div 1
set ms to (n - d) * 60
set m to ms div 1
return (d as text) & "°" & m & "’" & (round ((ms - m) * 60)) & "”"
end deci2degrees

--=============

on recolle(l, d)
local t
set AppleScript's text item delimiters to d
set t to l as text
set AppleScript's text item delimiters to ""
return t
end recolle

--=============

on nettoie()
set AppleScript's text item delimiters to ""
set my liste1 to {}
end nettoie

(* =============
An application named "NumbersAnglais.app" may be already running,
the script has activated "Numbers.app"
but the two apps named their process "Numbers".
We must check the "displayed name" to identity the correct one
*)
on whichProcess(the_App)
local listProcess, running, i
tell application "System Events"
set listProcess to (displayed name of processes)
repeat with i from 1 to count of listProcess
set running to 0
if item i of listProcess is (the_App & ".app") then
set running to i
exit repeat
end if
end repeat
end tell -- to System Events
return running
end whichProcess

--=============
--[/SCRIPT]


Yvan KOENIG (from FRANCE jeudi 13 mars 2008 10:38:17)

Oops

I used indexes in wrong order.

Here is a corrected and enhanced script.

It is able to convert decimal values into degrees
as well as
degrees vlues into decimals.

(*
Save the script with the name deci2degrees as an Application or an Application Bundle

If you want to use it with Numbers, put a copy of it in the folder
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

To use it,
Select the group of cells to convert
Copy to the clipboard
Select the destination cell
Goto Scripts > Numbers > deci2degrees
The converted values will be stored in the clipboard then pasted at the cursor.

If the original set of values contains the character "°" then
the script convert degrees in a decimal value
else
it converts decimal values in degrees, minutes, seconds
end if

It accepts the ' as well as the ’ as the minutes symbol.

Yvan KOENIG 13 mars 2008
*)
--(SCRIPT deci2degrees.app]
property paralist : {}
property liste1 : {}

--=============

on run
try
my emptyProperties()
set txtDatas to the clipboard as Unicode text
set my paralist to every paragraph of txtDatas
set srcNbRows to count of my paralist

if txtDatas contains "°" then (*
here convert degrees to deci *)
if txtDatas contains tab then (*
several columns *)
repeat with p in my paralist
copy my decoupe((p as text), tab) to end of my liste1
end repeat
set srcNbCols to count of item 1 of my liste1
repeat with i from 1 to srcNbRows
repeat with j from 1 to srcNbCols
if item j of item i of my liste1 is not "" then set item j of item i of my liste1 to my degrees2deci(item j of item i of my liste1)
end repeat -- j
set item i of paralist to my recolle(item i of my liste1, tab)
end repeat -- i
else (*
single column *)
repeat with i from 1 to srcNbRows
if item i of my paralist is not "" then set item i of my paralist to my degrees2deci(item i of my paralist)
end repeat
end if
else (*
here convert deci to degrees *)
if txtDatas contains tab then (*
several columns *)
repeat with p in my paralist
copy my decoupe((p as text), tab) to end of my liste1
end repeat
set srcNbCols to count of item 1 of my liste1
repeat with i from 1 to srcNbRows
repeat with j from 1 to srcNbCols
if item j of item i of my liste1 is not "" then set item j of item i of my liste1 to my deci2degrees(item j of item i of my liste1)
end repeat
set item i of paralist to my recolle(item i of my liste1, tab)
end repeat
else (*
single column *)
repeat with i from 1 to srcNbRows
if item i of my paralist is not "" then set item i of my paralist to my deci2degrees(item i of my paralist)
end repeat
end if
end if
set txtDatas to my recolle(paralist, return)
set the clipboard to txtDatas
my emptyProperties()
tell application "Numbers" to activate
tell application "System Events" to tell process (my whichProcess("Numbers")) to keystroke "v" using {command down}
on error
my emptyProperties()
end try
end run

--=============

on deci2degrees(n)
local d, ms, m
set d to n div 1
set ms to (n - d) * 60
set m to round (ms)
return (d as text) & "°" & m & "’" & (round ((ms - m) * 60)) & "”"
end deci2degrees

--=============

on degrees2deci(t)
local s
copy (t as text) to s
if s contains "°" then
set ll to my decoupe(s, "°")
set d to (item 1 of ll) as integer
set s to item 2 of ll
else
set d to 0
end if
if s contains "'" then
set {m, s} to my getMinutesSeconds(s, "'")
else if t contains "’" then
set {m, s} to my getMinutesSeconds(s, "’")
else
set m to 0
end if
if s > "" then if (text item -1 of s) is not in {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"} then set s to text 1 thru -2 of s
if s is "" then
set s to 0
else
set s to s as integer
end if
return (d + (m / 60) + (s / 3600)) as text
end degrees2deci

--=============

on getMinutesSeconds(t, d)
local ll
set ll to my decoupe(t, d)
return {(item 1 of ll) as integer, (item 2 of ll) as text}
end getMinutesSeconds

--=============

on decoupe(t, d)
local l
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to ""
return l
end decoupe

--=============

on recolle(l, d)
local t
set AppleScript's text item delimiters to d
set t to l as text
set AppleScript's text item delimiters to ""
return t
end recolle

--=============

on emptyProperties()
set AppleScript's text item delimiters to ""
set my liste1 to {}
set my paralist to {}
end emptyProperties

(* =============
An application named "NumbersAnglais.app" may be already running,
the script has activated "Numbers.app"
but the two apps named their process "Numbers".
We must check the "displayed name" to identity the correct one
*)
on whichProcess(the_App)
local listProcess, running, i
tell application "System Events"
set listProcess to (displayed name of processes)
repeat with i from 1 to count of listProcess
set running to 0
if item i of listProcess is (the_App & ".app") then
set running to i
exit repeat
end if
end repeat
end tell -- to System Events
return running
end whichProcess

--=============
--[/SCRIPT]

Yvan KOENIG (from FRANCE jeudi 13 mars 2008 21:12:4)

Hello

I really don't understand what afraid you with the script.
Read the explanations and apply them one by one.
You will get something appearing as a new menu in the application.

Yvan KOENIG (from FRANCE vendredi 14 mars 2008 09:39:25)

Does your formula work for negative numbers?

Where you afraid that it will destroy your machine?

No, it doesn' works correctly with negative values.
Before responding, I re-read my original post and discovered some extraneous characters which aren't in my spreadsheet.
It seems that the message parser striked again.

Here is an enhanced formula.

=IF(A<0,"-"&INT(ABS(A))&"°"&INT(MOD(ABS(A),1) 60)&"'"&ROUND(MOD(MOD(ABS(A),1)*60,1)*60,0),INT(A)&"°"&INT(MOD(A,1)*60)&"'"&ROUN D(MOD(MOD(A,1)*60,1)60,0))

I hope that this time there gremlin will not strike again 😉

Yvan KOENIG (from FRANCE lundi 26 mai 2008 09:23:01)

Olsin,

It looks like the second part, "visa vera", of your question has gone unanswered.

But first, since I like minutes and seconds to show two digits, Yvan's formula has been modified to pad leading 0's in these positions and '' (double apostrophes, not single quote) have been added for for seconds. Besides, my solution depends on this feature. Yvan's formula has become:

=IF(ISBLANK(A2),"",IF(A<0,"-"&INT(ABS(A))&"°"&RIGHT("00"&INT(MOD(ABS(A),1) 60),2)&"'"&RIGHT("00"&ROUND(MOD(MOD(ABS(A),1)*60,1)60,0),2) &"''",INT(ABS(A))&"°"&RIGHT("00"&INT(MOD(ABS(A),1) 60),2)&"'"&RIGHT("00"&ROUND(MOD(MOD(ABS(A),1)*60,1)60,0),2)&"''"))

Reversing this process, i.e. going from ddºmm'ss'' (padded zeros for minutes and seconds must be included) to decimal, the formula is:

=IF(ISBLANK(A2),"",IF(LEFT(A,1)="-",(ABS(VALUE(LEFT(A,SEARCH("º",A)-1)))+ VALUE(MID(A,SEARCH("º",A2) 1,2))/60+VALUE(MID(A,SEARCH("'",A)+1,2))/3600)*(-1),ABS(VALUE(LEFT(A,SEARCH("º", A)-1)+VALUE(MID(A2,SEARCH("º",A2)+1,2))/60+VALUE(MID(A,SEARCH("'",A)1,2))/3600))))

These formulas are somewhat lengthy, but I hope you find them satisfactory.

pw



Message was edited by: pw1840

Thanks! This has been a huge help to me (I am a professional geographer and have managed over the years to do this in Excel - but as a reconvert back to mac (I had an LC.......) and a lover of iwork numbers has been a little bit of a challenge.

One small prob though - Icut and past your formula and whilst it now works for negative numbers, it does not work for positive ones. I just get a string with zeroes in it - any idea what I have done wrong this end?
These formulas are way too useful to give up on!!!

Again many thanks

0.5 0°30'0 0°00'00''
-0.5 -0°30'0 -0°30'00''
0.25 0°15'0 0°00'00''
-0.25 -0°15'0 -0°15'00''

Thanks Yvan - much appreciated! It is a mac nothing I can do can ever destroy it 🙂, apart from backing over it in my truck

Olsin,

I'm sorry you're having trouble, but if you copied and pasted the formula from my previous response it should work. I performed that same procedure going from my test table to my response. You can see that the results are correct in the screenshot. On the chance that something went awry, I'll repeat the formula here:

=IF(ISBLANK(A2),"",IF(A<0,"-"&INT(ABS(A))&"°"&RIGHT("00"&INT(MOD(ABS(A),1) 60),2)&"'"&RIGHT("00"&ROUND(MOD(MOD(ABS(A),1)*60,1)*60,0),2)&"''",INT(ABS(A))&"° "&RIGHT("00"&INT(MOD(ABS(A),1)*60),2)&"'"&RIGHT("00"&ROUND(MOD(MOD(ABS(A),1)*60, 1)60,0),2)&"''"))

Try it again and let us know what happened. Thanks,

pw

Hi PW,
Ok so now it is working, thanks! However the vice-versa one is not - it gives me a little red triangle and states SEARCH Cannot Find "º".

I have used the output cells created by the 1st formula and get this. I then tried just typing in a new DMS and get the sane result.

Thanks again for all the help in this, I am sure it is just something minor

Cheers

Olsin,

Glad we got part of this working for you. Was the reverse working before? Did you place the reverse formula in the column immediately to the right of the column with output created by the first formula? If not, you will have to go through and change all the cell references in the formula from column A (or whatever it now is) to the correct column designation. Or perhaps, as with the first formula, something did not copy correctly. Let's try the reverse formula again:

=IF(ISBLANK(A2),"",IF(LEFT(A,1)="-",(ABS(VALUE(LEFT(A,SEARCH("º",A)-1))) VALUE(MID(A,SEARCH("º",A2)+1,2))/60+VALUE(MID(A,SEARCH("'",A)+1,2))/3600)*(-1),A BS(VALUE(LEFT(A,SEARCH("º",A)-1)+VALUE(MID(A2,SEARCH("º",A2)+1,2))/60+VALUE(MID( A,SEARCH("'",A)1,2))/3600))))

pw