I was missing some brackets. This works: =IF(MID(A2,LEN(A2),1)="N",MID(A2,1,2)+MID(A2,4,6)/60,IF(MID(A2,LEN(A2),1)="S",-(MID(A2,1,2)+MID(A2,4,6)/60),IF(MID(A2,LEN(A2),1)="E",MID(A2,1,3)+MID(A2,5,6)/60,-(MID(A2,1,3)+MID(A2,5,6)/60))))
52:18.500N returns 52.3083 010:43.667E returns 10.7278 34:27.1200S returns -34.4520 147:32.3400W returns -147.5390 45:10.000N returns 45.1667 045:10.000E returns 45.1667 Cheers Derek 人生は短いです：一日をつかむ -----Original Message----- From: Derek Ruddock [mailto:derek.rudd...@optus.com.au] Sent: Monday, 20 December 2010 4:08 PM To: martin.kopp...@gmx.de; xcsoar-user@lists.sourceforge.net; xcsoar-user@lists.sourceforge.net 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 人生は短いです：一日をつかむ -----Original Message----- From: martin.kopp...@gmx.de [mailto:martin.kopp...@gmx.de] Sent: Monday, 20 December 2010 9:51 AM To: xcsoar-user@lists.sourceforge.net Subject: [Xcsoar-user] coordinate conversion tool? A bit off topic, though: Does any of you know a tool to batch calculate decimal degrees from the coordinates as used in the CAI format? Like this: 010:43.667E ; 52:18.500N ----becomes---> 10,7277 ; 52,3083 Excel or OO seem to have issues with it (or do I have...?). Martin --- ------------------------------------------------------------------------------ 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 Xcsoar-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/xcsoar-user ------------------------------------------------------------------------------ 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 Xcsoar-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/xcsoar-user ------------------------------------------------------------------------------ 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 Xcsoar-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/xcsoar-user
