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