How to easily convert Decimal Degrees to Degrees Minutes Seconds [in Excel].

This is one of those one-off moments that shows us, yet again, why we love using Micorosft Excel.

When not in front of our computers working on an Excel spreadsheet, a favourite pastime of ours is Geocaching. Never heard of geocaching? Click this link to find out more… (be warned… It is VERY addictive!!).

Simply put, geocaching is a worldwide treasure hunt that anyone can join in. All you need is a GPS (which is a standard feature in most modern mobile phones) and you can join in.

Anyway, on one of these “hunts”, we needed to convert some longitude and latitude coordinates from Decimal Degrees to Degrees Minutes Seconds. A quick internet search and we came across this Microsoft Excel formula. It allows you to convert Decimal Degrees to Degrees Minutes Seconds within seconds.

To create your own conversion spreadsheet – simply copy and paste the formula below into an Excel spreadsheet 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 shows the seconds being a decimal of the minutes component.

Help support ExcelSuperSite – T-Shirts and Merchandise

Do you like this template and would like to help support us at ExcelSuperSite to continue to share great templates and content such as this – why not take a look at some of the t-shirts and merchandise we have on offer – click any of the images below to visit our store.

Please note – our T-shirts and merchandise are hosted on an external “Print on Demand” website Redbubble/ExcelSuperSite so when you click an image below you will be automatically taken to our store on that site.

Continue the Discussion

Do you use Excel to convert coordinates? 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.