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.
-------------------------------------------------------