Oh my poor blog.
Going to try and start posting semi-regularly again (for really reals this time).
As proof, here's a thing:
Something sort-of interesting came up at work today.
I had to create a one-stop Excel function to convert DMS Lat/Long to their Decimal equivalents.
Ala:
52° 58' 44.889" N
Into
52.979136
And
122° 33' 37.929" W
Into
-122.560536
Almost all the examples I could find relied on one, or a combination of;
- Pre-formatting the data using text-to-columns
- Used hard-coded numbering, or modifying the original values into some other format.
- Or VBS script(s) to do it
My restrictions were;
- I can't modify the original data
- I "must" reference the original data (can't copy/paste, modify, then use)
- It must be easy to spot problems with the reference data
Microsoft has a script, but it only worked for about 15 minutes, and then broke. And $DAYJOB restrictions meant I couldn't make the VBS portable with the end result anyway.
Suffice to say, neither option suited my case, and it seemed a problem that should be solvable with a single function.
What I learned: Excel can do stupidly interesing stuff, but holy crap - if they would just integrate regex support directly into excel (read: Not as a VBS plugin) for normal human beings, my life would have been much easier.
Function for Latitude:
=IF(LEFT(A10,1)="-","-",IF(RIGHT(A10,1)="S","-",""))
&LEFT(A10, FIND("°", A10)-1)
+(MID(A10,FIND("°",A10)+1,FIND("'",A10)- FIND("°", A10)-1)/60)
+(MID(A10,FIND("'",A10)+1,FIND("""",A10)- FIND("'", A10)-1))/3600
To break it down;
If the A10 cell starts with a '-', or ends with a 'S'outh, add a negative to the decimal output.
Parse number for Degrees
Parse number for Minutes, convert
Parse number for Seconds, convert
Function for Longitude is much the same. If check just converts 'W'est to negative instead.
=IF(LEFT(B10,1)="-","-",IF(RIGHT(B10,1)="W","-",""))
&(LEFT(B10, FIND("°", B10)-1)
+(MID(B10,FIND("°",B10)+1,FIND("'",B10)- FIND("°", B10)-1)/60)
+(MID(B10,FIND("'",B10)+1,FIND("""",B10)- FIND("'", B10)-1)/3600
Pro's:
Don't need VBS, or to split the lat/long into separate cells
Spacing between values doesn't matter (empty space is easy to deal with, if excel doesn't automatically).
Con's:
It is fragile. Breaks when someone uses the wrong delimiter for degrees, minutes seconds (°, ', "), but those are easy to find and fix - someone that fails at their data entry is not my problem in this case.
Though the fragility is my 'detect problem' case. #VALUE! cells are easily parsed out of the output, for which I can take it up with the data provider in the first place.
No numerical errors so far across 132 pairs of DMS lat/long values. Hooray.
Coming up next, probably a rant about TCP Bandwidth Delay Product.
