Try this. The problem is that , just from the number you cannot determine
automatically if it is a lat or long.
The formula below adds both onto the string. It's straightforward to change it
to suit your purposes.
=IF(A9>0,LEFT(A9,FIND(".",A9)-1)&":"&TEXT(RIGHT(A9,LEN(A9)-FIND(".",A9)+1)*60,"00.000")&"N/E",LEFT(A9,FIND(".",A9)-1)*-1&":"&TEXT(RIGHT(A9,LEN(A9)-FIND(".",A9)+1)*60,"00.000")&"S/W")
52.308 returns 52:18.500N/E
10.728 returns 10:43.667N/E
-34.452 returns 34:27.120S/W
-147.539 returns 147:32.340S/W
45.167 returns 45:10.000N/E
45.167 returns 45:10.000N/E
Cheers
Derek
人生は短いです：一日をつかむ
-----Original Message-----
From: Reg Moore [mailto:[email protected]]
Sent: Monday, 20 December 2010 5:54 PM
To: Derek Ruddock
Subject: RE: [Xcsoar-user] coordinate conversion tool?
Derek,
Can we have one to convert decimals to deg min and sec i.e. in reverse to
the formula below
Reg Moore
-----Original Message-----
From: Derek Ruddock [mailto:[email protected]]
Sent: Monday, 20 December 2010 15:38
To: [email protected]; [email protected];
[email protected]
Subject: Re: [Xcsoar-user] coordinate conversion tool?
If you want a formula that caters for degrees west and south, the following
will do it.
This assumes that the field in A1 is in Cambridge format, ie DD:MM.SSSSH
where H = N or S, or DDD:MM.SSSH where H is E or W.
=IF(MID(A1,LEN(A1),1)="N",MID(A1,1,2)+MID(A1,4,6)/60,IF(MID(A1,LEN(A1),1)="S
",-MID(A1,1,2)+MID(A1,4,6)/60,IF(MID(A1,LEN(A1),1)="E",MID(A1,1,3)+MID(A1,5,
6)/60,-MID(A1,1,3)+MID(A1,5,6)/60)))
52:18.500N returns 52.30833333
010:43.667E returns 10.72778333
34:27.1200S returns -33.548
147:32.3400W returns -146.461
Cheers
Derek
------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Xcsoar-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/xcsoar-user
