In search of ... a way (Excel macro?) to convert Lat-Long co-ordinates

[barney]

BD (Baby Daughter) has been tasked with converting some Lat-Long deg-min-sec coordinates to decimal.  There are a number of Web services to accomplish this, several with .gov TLDs, more with .EDU TLDs.

There are also several forae that discuss Excel macros to accomplish this end. 

However, nothing I've found so far really fills the bill  .

BD's requirements are to take a [significant] number of degrees-minutes-seconds values and convert them to a decimal equivalent.  I'm thinkin' it would be nice to be able to reverse that process, as well.

It's been a decade since I did anything with Excel (Office 2000), so I'm more than a bit rusty  .

Checked DC - several discussions involved - but nothing I found seems to cover this.

Anyone have a pointer to such a bit of code?  (I'd personally prefer an application for this, but considering the number of conversions to be made - and corporate paranoia - a spreadsheet solution would be better).  (If it matters, they are using Office 2003, soon to upgrade to 2007:  really progressive  .)s

[skwire]

Do you just have a simple list of coordinates that need converting?

[barney]

Nope.

This is an ongoing thing, with new locations on a semi-regular basis.  Might be one (1) a week, might be a hundred.  That's why I'm looking at the spreadsheet resolution - if it's just one (1), http://transition.fc...DDDMMSS-decimal.html or http://www.direction...te/latlong-converter would work just fine.  However multiple tens of coordinates would be quite time-consuming.

Actually, I thought perhaps it could be done with AHK or AutoIt!, but corporate paranoia reigns supreme in this arena ... BD is not allowed any software on her laptop that was not installed by her IT group  .  Otherwise, I have an old VB (5.0) - if I can find it  - app that did exactly that (from my MCI days).

[Renegade]

And...

http://support.microsoft.com/kb/213449

Tada~!

They've already got it done for you.

Or here if you like:

http://www.ozgrid.co...p?t=25460&page=1

[CWuestefeld]

The formula for this is pretty simple. If you have separate values for Deg, Min, and Sec, then the decimalized equivalent is

Code: Text [Select]

1. Deg+(Min+Sec/60)/60

I've attached an example using Excel formulas.

If you've got to parse out the three values from a single string, that's where all the work is -- but I can't tell you exactly how to do that without seeing the input strings.

[barney]

but I can't tell you exactly how to do that without seeing the input strings.

-CWuestefeld (September 19, 2011, 12:47 PM)

Aye, and there's the rub  .  BD is getting these values from third parties and has no control over the format used.  So far, she's seen numbers with deg-min-sec symbols, some with a minus (-) prefix, some with a N/S/E/W suffix, some w/o symbols but separated by dashes, some with dot (.) separators, some with deg-min-sec spelled out ... a real mishmash.  A simple deg-min-sec conversion I could handle - as could she, for that matter - but neither of us is conversant with VBA enough to be able to set triggers for all the variants to date, much less what might come.  If she can paste a list into a column, then have that column transcribed into another column in the proper format, all is well.  However, if she has to correct the format prior to that paste, nothing's been gained.

I know VBA is powerful enough to manage this, but I'm not familiar enough with its syntax to build such a parser on short order, nor do I have the time to study VBA for the purpose.  Time is the crippler here, for both of us.  That's why we're trying to find a pre-built solution .  Guess the solution will be to set a separate sheet for each parsing mode  , but while that will help reduce the time element, it's not really a satisfactory solution  .

[4wd]

Could we have a sample of the various formats you're receiving them in, say 10 lines or so?

You may also want to take a look at GPSBabel, if you can put each format of coord into it's own text file, (ie. one file for each format type), you may be able to batch convert.  (EDIT: GPSBabel is portable, no need to install it, run it from a Flash drive.)

Also, there is GPS Visualizer - online coords converter/plotter/etc.

[barney]

Thanks, all, but this has been rendered moot  .

BD told me when she got home from work today that they have given this as a project to someone else.  What she was expected to do as an incidental task has now become a full time effort for someone else  .

On the plus side, we both have picked up a bit of knowledge - thanks to you all - that can only help in the future. The sudden reassessment and reassignment is galling, nonetheless <gr-r-r-r!/>.