Easily Convert Decimal Degrees to Degrees Minutes Seconds [in Excel]


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.

 

Explore our ExcelSuperSite Excel spreadsheet templates and downloads

Some of our most popular downloads are ...

image - Excelsupersite 12 month calendar