|
Small programming task in Excel VBA
On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon"
wrote in microsoft.public.excel.programming: I am somewhat familiar with VB but never written anything for Excel at all, so please be patient with me. I need to run a macro or some VBA code to run on an Excel spreadsheet. The spreadsheet consists of a couple of columns that contain latitudes and longitudes. The latitudes (and longitudes) are in decimal format e.g +26.1234 which needs to be converted into a degrees minutes and seconds (DMS) format, the + or - sign signals either North or South latitude, so the converted colum would look like 260724N which is 26 degrees, 07 minutes, and 24 seonds North. [snip] No VBA code necessary. A formula I came up with quickly is: =(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A2),1)*3600,60)))*SIGN(A2 ) will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234 Formatting that with a hemisphere is left as an exercise for the reader. -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
Small programming task in Excel VBA
John wanted to show -ve as S, +ve as N.
On my post, if you don't want the dots between the degrees, minutes and secs, use =TEXT(ABS(A1/24),"[h]mmss") & IF(A1<0,"S","N") -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Michael Bednarek" wrote in message ... On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon" wrote in microsoft.public.excel.programming: I am somewhat familiar with VB but never written anything for Excel at all, so please be patient with me. I need to run a macro or some VBA code to run on an Excel spreadsheet. The spreadsheet consists of a couple of columns that contain latitudes and longitudes. The latitudes (and longitudes) are in decimal format e.g +26.1234 which needs to be converted into a degrees minutes and seconds (DMS) format, the + or - sign signals either North or South latitude, so the converted colum would look like 260724N which is 26 degrees, 07 minutes, and 24 seonds North. [snip] No VBA code necessary. A formula I came up with quickly is: =(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A 2),1)*3600,60)))*SIGN(A2) will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234 Formatting that with a hemisphere is left as an exercise for the reader. -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
Small programming task in Excel VBA
Thanks for the help guys, appreciate it!
here is the formula I am using, combining both of your suggestions... =(INT(ABS(C3))*10000+TEXT(INT(MOD(ABS(C3),1)*60)," 00")*100+INT(MOD(MOD(ABS(C 3),1)*3600,60)))*SIGN(C3)& IF(C3<0,"S","N") a couple of issues that I have tried to fix and can't seem to 1) I want the degrees portion to always have two digits, ie 5.1234 converted will read 050724N, how do I force it to lead with 0 (if req'd)? 2) When I use the formula with a negative value, the converted value still has the negative (-) sign in front, how do I get rid of that? thanks again John J "Bob Phillips" wrote in message ... John wanted to show -ve as S, +ve as N. On my post, if you don't want the dots between the degrees, minutes and secs, use =TEXT(ABS(A1/24),"[h]mmss") & IF(A1<0,"S","N") -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Michael Bednarek" wrote in message ... On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon" wrote in microsoft.public.excel.programming: I am somewhat familiar with VB but never written anything for Excel at all, so please be patient with me. I need to run a macro or some VBA code to run on an Excel spreadsheet. The spreadsheet consists of a couple of columns that contain latitudes and longitudes. The latitudes (and longitudes) are in decimal format e.g +26.1234 which needs to be converted into a degrees minutes and seconds (DMS) format, the + or - sign signals either North or South latitude, so the converted colum would look like 260724N which is 26 degrees, 07 minutes, and 24 seonds North. [snip] No VBA code necessary. A formula I came up with quickly is: =(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A 2),1)*3600,60)))*SIGN(A2) will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234 Formatting that with a hemisphere is left as an exercise for the reader. -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
Small programming task in Excel VBA
Why go for a big hairy formula when Bob's approach works fine
=TEXT(ABS(C3/24),"[hh]mmss")&IF(C30,"N","S") The above will lead with zero does not put in a negative sign Regards, Tom Ogilvy John Johndon wrote in message ... Thanks for the help guys, appreciate it! here is the formula I am using, combining both of your suggestions... =(INT(ABS(C3))*10000+TEXT(INT(MOD(ABS(C3),1)*60)," 00")*100+INT(MOD(MOD(ABS(C 3),1)*3600,60)))*SIGN(C3)& IF(C3<0,"S","N") a couple of issues that I have tried to fix and can't seem to 1) I want the degrees portion to always have two digits, ie 5.1234 converted will read 050724N, how do I force it to lead with 0 (if req'd)? 2) When I use the formula with a negative value, the converted value still has the negative (-) sign in front, how do I get rid of that? thanks again John J "Bob Phillips" wrote in message ... John wanted to show -ve as S, +ve as N. On my post, if you don't want the dots between the degrees, minutes and secs, use =TEXT(ABS(A1/24),"[h]mmss") & IF(A1<0,"S","N") -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Michael Bednarek" wrote in message ... On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon" wrote in microsoft.public.excel.programming: I am somewhat familiar with VB but never written anything for Excel at all, so please be patient with me. I need to run a macro or some VBA code to run on an Excel spreadsheet. The spreadsheet consists of a couple of columns that contain latitudes and longitudes. The latitudes (and longitudes) are in decimal format e.g +26.1234 which needs to be converted into a degrees minutes and seconds (DMS) format, the + or - sign signals either North or South latitude, so the converted colum would look like 260724N which is 26 degrees, 07 minutes, and 24 seonds North. [snip] No VBA code necessary. A formula I came up with quickly is: =(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A 2),1)*3600,60)))*SIGN(A2) will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234 Formatting that with a hemisphere is left as an exercise for the reader. -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
Small programming task in Excel VBA
John,
This really is overkill. My formula is much simpler, does not have a leading negative sign, and Tom showed you how to get a leading 0 in the degrees by adding another digit to the hour format =TEXT(ABS(A1/24),"[hh]mmss") & IF(A1<0,"S","N") Bigger is not always better! -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "John Johndon" wrote in message ... Thanks for the help guys, appreciate it! here is the formula I am using, combining both of your suggestions... =(INT(ABS(C3))*10000+TEXT(INT(MOD(ABS(C3),1)*60)," 00")*100+INT(MOD(MOD(ABS(C 3),1)*3600,60)))*SIGN(C3)& IF(C3<0,"S","N") a couple of issues that I have tried to fix and can't seem to 1) I want the degrees portion to always have two digits, ie 5.1234 converted will read 050724N, how do I force it to lead with 0 (if req'd)? 2) When I use the formula with a negative value, the converted value still has the negative (-) sign in front, how do I get rid of that? thanks again John J "Bob Phillips" wrote in message ... John wanted to show -ve as S, +ve as N. On my post, if you don't want the dots between the degrees, minutes and secs, use =TEXT(ABS(A1/24),"[h]mmss") & IF(A1<0,"S","N") -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Michael Bednarek" wrote in message ... On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon" wrote in microsoft.public.excel.programming: I am somewhat familiar with VB but never written anything for Excel at all, so please be patient with me. I need to run a macro or some VBA code to run on an Excel spreadsheet. The spreadsheet consists of a couple of columns that contain latitudes and longitudes. The latitudes (and longitudes) are in decimal format e.g +26.1234 which needs to be converted into a degrees minutes and seconds (DMS) format, the + or - sign signals either North or South latitude, so the converted colum would look like 260724N which is 26 degrees, 07 minutes, and 24 seonds North. [snip] No VBA code necessary. A formula I came up with quickly is: =(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A 2),1)*3600,60)))*SIGN(A2) will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234 Formatting that with a hemisphere is left as an exercise for the reader. -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
| All times are GMT +1. The time now is 04:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com