How to easily convert Decimal Degrees to Degrees Minutes Seconds [in Excel].
Came across this Microsoft Excel formula the other day which easily allows you to convert Decimal Degrees to Degrees Minutes Seconds within seconds.
Latitude and Longitude
It is very easy to use – simply copy and paste the formula into Excel and change the [Lat Cell] or [Long Cell] text within the formulas to point to the cells with the Decimal Degree values in them.
Latitude.
=CONCATENATE(TEXT(ROUNDDOWN(ABS([Lat Cell]),0),"00"),"° ",TEXT(ROUNDDOWN(ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))*60),0),"00"),"' ",TEXT(TRUNC((ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))*60)-ROUNDDOWN(ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))*60),0))*60,2),"00.00"),"""",IF([Lat Cell]<0," S"," N"))
Longitude.
=CONCATENATE(TEXT(ROUNDDOWN(ABS([Long Cell]),0),"000"),"° ",TEXT(ROUNDDOWN(ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))*60),0),"00"),"' ",TEXT(TRUNC((ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))*60)-ROUNDDOWN(ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))*60),0))*60,2),"00.00"),"""",IF([Long Cell]<0," W"," E"))
The image below shows 2 different variants of the above formulas. The first for both latitude and longitude shows examples of the formula exactly as above. The second show the seconds being a decimal of the minutes component.
Convert Decimal Degrees to Degrees Minutes Seconds
Continue the Discussion
Do you use Excel to convert co-ordinates? If so, how do you do it? Did you use a similar method to the above or another method? Continue the discussion and add your thoughts in the comments section at the bottom of this article.
Please Share
If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter and/or Linkedin.
Some of our most popular downloads are ...
