Decimal Degrees to Degree Minutes Seconds Conversion VBA Stripping Leading Zeros

[JTHOM]

I am using Excel to average angles derived from GPS measurements.

Angles are imported into Excel with no spaces after the ° and ' symbols; ie 123°05'09".

I am using VBAs obtained from the following link:

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

to convert angles between decimal degrees and degree minutes seconds.

These VBAs require a space after the ° and ' symbols; ie 123° 05' 09", which I create with the SUBSTITUTE function.

Angles are then converted to decimal degrees averaged then converted back to degrees minutes seconds.

Unfortunately the final step strips leading zeros before minutes and seconds that are single digit numbers; ie 123° 5' 9".

I need to retain the leading zeros so i can import these values directly into another package.

Does anyone have any suggestions?

Thanks in advance.

[Leith Ross]

Hello JTHOM,

Welcome to he Forum!

This version of the macro will insure there is always 2 zeroes in each place holder when the value is less than 10.

Please Login or Register  to view this content.

[JTHOM]

Leith,

Thanks for your reply.

The VBA you attached returned the following results:

Input Result Actual
213.1505556 213° 9' 0 213° 09' 02"
240.0552778 240° 3' 0 240° 03' 19"
160.2605556 160° 16' 0 160° 15' 38"

I comment the seconds values are incorrectly rounded, the leading zeros for single digit minute values and the seconds symbols are dropped.

Could you please advise me if this only apparant on my end.

[Leith Ross]

Hello JTHOM,

You're correct. The code does round the seconds to zero. That appears to be bad editing on my part. Try this version. It appears to work correctly on my end.

Please Login or Register  to view this content.

[JTHOM]

Leith,

Thanks alot works great.

As the " symbol was still not apparent I added it at the very end of your VBA.

The final product is attached below:

Please Login or Register  to view this content.

Thanks again.

[SOLVED]