Replacing Minus Signs on Angles with N/E/S/W Compass Points

Dear Group

I am in the middle of a large project calculating arc lengths over an ellipsoid earth form in Excel 2010. So far, I have got the macro for the formula in a module based on the Vincenty formula. It is supposed to give me data very accurately. However, for my co-ordinates, it cannot handle minus signs “-“ in front of the data (e.g. for west or south directions).

My original data is like this in 4 columns:

Lat1: 10.235145, Long1: -102.256454, Lat2: 56.441134, Long2: 89.5484648

Now this formula needs me to convert the values to Degrees, Minutes, Seconds.

I tried using this: =DD2DMS(Degrees), and it gave the following, for example:

-23.61549186 becomes -23° 36' 55.77''

However, the formula cannot handle correctly any values with minus signs, so this does not help.

I need a way to convert an angle from decimal values into Degrees, Minutes, Seconds, WITH a compass sign on the end of each value. E.g.

-23.61549186 to

23° 36' 55.77'' S, or
23~ 36' 55.77'' S

How can I assign a letter to the end of each co-ordinate value and remove the “-“ signs from the front?

Can anybody help. I am totally stuck.

Sincerely

Keith

Dear SHG,

Thank you very much. You have just solved it.

The same person that used the code in a prepared excel spreadsheet used the following Formula to give the result:

=distvincenty(signit(B2),signit(C2),signit(B3),signit(C3))


You used:

=distvincenty(A1, B1, C1, D1)

Your formula works. The one they were using in their default template does not work for decimals or with minus figures.

Thank you for your solution.