I could use a bit o help please

I need a tool that works in MS excel to handle a very large amounts of data.
I am prohibited from simply sending the actual coordinates, but I can use made up random ones all day long. The functionality I need is paste my data into Collum A and B and get out puts in C and D LAT LONG -> LAT LONG

Sheet 1 DD to DMS Sheet 2 DMS to DD

Senario:

I need to take data that is geo coordinates latitude and longitude from Degrees, minutes, seconds(dot) numbered fraction (.75 not ¾) and translate it to Decimal degrees.

I also need to be able to take decimal degrees and reverse the process…

Take decimal degrees and output degrees, minutes, seconds (dot) numbered fraction as shown above.

My Decimal Degrees are in ##.XXXXXX format. No follow on letter appears.

The LAT and LONG are in Separate Collums
If the value is Negative then it is either S or W as applicable for the column
(latitude, or longitude)

This process should actually be much easier than going from DMS to DD, and the values that are ###.XXXXXX format (100 or over)or #.XXXXXX (under 10) I think can be managed by the same format.

OUTPUT must be DDMMSS.XXH and depending on LAT/LONG be N/S or E/W

DMS to DD
My data is in the following format:

Latitude

DDMMSS.XXH H is a letter, and is always either N,or S



Longitude

DDMMSS.XXH H is a letter, and is always either E, or W.
The LAT and LONG are in Separate Collums

Note occasionally a placeholder zero (or two zeros) is used, or the coordinates are +100 deg E or W, in that caseDDMMSS.XXH is used
This is the exception rather than the rule… if it is easy to write a new check for the coordinates 100 or over or with the place holder Zero that is nice, but my current areas really only need the format DDMMSS.XXH supported.

Carried out a max of 6 decimal places. OUTPUT DD.XXXXXX no follow on letter.

Here is some sample code that I was given but have no idea how to implement, I see what it does, but don’t know how to dump it into excel:

DD-> DMS:

strDegs=numDD.Truncate
strMins=((numDD – strDegs.AsNumber) * 60).Truncate
strSecs=((numDD – strDegs.AsNumber) * 3600) mod 60

if numDD<0then
strHem=”S”
else
strHem=”N”
end

Note sample does not address E or W, but Im not really sure I need to address N or S or E or W in either case.

DMS-> DD: Carry out to max of 6 decimal places

For latitudes…

strDegs=strDMS.Left(2)
strMins=strDMS.Middle(2,2)
strSecs=strDMS.middle(4,2)
strHem=strDMS.Right(1)

For Longitudes…
strDegs=strDMS.Left(3)
strMins=strDMS.Middle(3,2)
strSecs=strDMS.middle(5,2)
strHem=strDMS.Right(1)

numDD=strDegs.AsNumber + (strMins.AsNumber/60) + strSecs.AsNumber/3600)
if((strHem=”S”) or strHem = “W”)) then numDD=-1.0 *numDD
end


I don’t think this code takes into account a check for Longitudes that have no leading zeros… which I think I need addressed.
-------------------------------------------------------

DD to DMS partly working below:

Function Convert_Degree(Decimal_Deg) As Variant
With Application
'Set degree to Integer of Argument Passed
Degrees = Int(Decimal_Deg)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
Minutes = (Decimal_Deg - Degrees) * 60
'Set seconds to 60 times the number to the right of the
'decimal for the variable Minute
Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.000")
'Returns the Result of degree conversion
'(for example, 10.46 = 10~ 27 ' 36")
Convert_Degree = Degrees & Int(Minutes) & Seconds
End With



This works as a VB module in Excel I removed the Deg, Min and Sec marks as they are unwanted int the output. (you can see the exmple in the formula)

Here is my problem. If DD, MM, or SS.000 = <10 then I lose a needed placeholder.

So placing the value in cell A1 and then [=Convert_Degree(A1)] minus brackets I get:

34.75 = 34450.000 I need to have the leading zero in the SS position.

or 2.0523 = 238.280 and here I have 3 bad fields, all needing a zero for a corrected output of
020308.280

I also need to still add the arguemnt to add a N or S / E or W to the end of the output.

Ultimately Collum header A1 / B1 would read LAT or LONG and be the defining variable to answer if N or S is used or W or E is used

Something like:

if Degrees<0 and A1 = LAT then Convert_Degree = Degrees & Int(Minutes) & Seconds "S"
else if A1 = LAT then Convert_Degree = Degrees & Int(Minutes) & Seconds "N"


if Degrees<0 and B1 = LONG then Convert_Degree = Degrees & Int(Minutes) & Seconds "W"
else if B1 = LONG thenConvert_Degree = Degrees & Int(Minutes) & Seconds "E"



Anyone have any ideas from this point? I will try to come up with a list of values to play with. It like I have a map but I'm without my glases.


PS I did search the forum fist but my formatting needs are unique.

The paste in of the data didnt make it look pretty, but the point gets across well enough If you donwload the link below you get the same as this post...


DDMMSS.01N DDMMSS.02E

DD INPUT DESIRED OUTPUT DMS
LAT LONG LAT LONG
-1.065600 100.020100 010356.1600S 1000112.3600E
23.009700 -2.995200 230034.9200N 0025942.7199W
34.562578 34.750789 343345.2808N 0344502.8408E
-0.010101 -0.010101 000036.3636S 0000036.3636W
89.999999 99.999999 895959.9963N 0995959.9963E

NOte the 4th line creates a problem for me because the INT deg is zero, I think I have to check the whole value. I could live without the N,S,E,W for the moment. But I would need a step to simply add the letter at then end of the collum, since my data sets are normally well positioned into their respective hemispheres... ie most are in the 5-60N/S and 5-150E/W range

I know EXCEL is none to forgivinging on having zeros lead numbers.... Wroking a text cell as a number and thenputting it backinto a text cell.
DMS INPUT DESIRED OUTPUT DD
LAT LONG LAT LONG
340459.990000N 453030.500000W 34.083331 -45.508472
253030.300000S 1450002.300000E -25.508417 145.000639
000036.3636S 000036.3636W -0.010100 -0.010100
555555.550000N 0555555.550000E 55.932097 55.932097
454545.030000N 0990234.660000E 45.762508 99.042961
Note I have different providers for this data... one uses leading zeros, the other does not.

The chart does not need to jointly recognize this...

And Also I have the problem that providers dump the N/S/E/W either at the beginning or end.

It seems that the solution should be simple... if the block contains a S or W then output=ouput(*-1)
This could be alievated by taking all data, dumping it into notepat and do a find-replace and delete the letter.... I would be running this process once a month... so an operation like that wont be too much of a haslte.



one at a time no negs, no letter, but does the job... maybe sample code from here?


16kb of sample xls file of above info in this post