How to use Excel to Convert Decimal Latitude and Longitude to DMS for Adobe Bridge
So here's an issue that I've struggled with for a a few years.
My camera does not store GPS co-ordinates as part of the photo, so I have to enter them manually in Adobe Bridge. This involves finding the latitude and longitude in digital format in Google maps, then finding a page that will convert them into DMS (Degree,Minute,Second) format.
But that's not all. Adobe bridge does not accept co-ordinates as standard DD° MM' SS'' [NS|EW] format. It has to be converted back into a kind of digital format again: dd,mm.sshh[NS|EW] (hh is hundredths of a second).
I've been doing the conversion manually up until now, but I've figured out an Excel function that will do the trick.
It turns out that the conversion of digital to DMS is simple enough - all you do is divide by 24, and display the result in a Time format: [hh],mm.ss.00. Putting the square brackets around the hh displays the total number of hours if there are more than 24 - eg without the square brackets, 56 hours would be displayed as 8 (2 days and 8 hours).
There are two issues with this:
My camera does not store GPS co-ordinates as part of the photo, so I have to enter them manually in Adobe Bridge. This involves finding the latitude and longitude in digital format in Google maps, then finding a page that will convert them into DMS (Degree,Minute,Second) format.
But that's not all. Adobe bridge does not accept co-ordinates as standard DD° MM' SS'' [NS|EW] format. It has to be converted back into a kind of digital format again: dd,mm.sshh[NS|EW] (hh is hundredths of a second).
I've been doing the conversion manually up until now, but I've figured out an Excel function that will do the trick.
It turns out that the conversion of digital to DMS is simple enough - all you do is divide by 24, and display the result in a Time format: [hh],mm.ss.00. Putting the square brackets around the hh displays the total number of hours if there are more than 24 - eg without the square brackets, 56 hours would be displayed as 8 (2 days and 8 hours).
There are two issues with this:
- There is an extra decimal point between the seconds and the hundredths of a second. Excel won't let you enter the format without that extra decimal point.
- The compass point, N, S, E or W is not displayed.
So we need to do three calculations and string them together.
First off, enter the following fields in Excel:
In cell C2, enter the following formula:
=TEXT(ABS(B2/24),"[hh],mm.ss")&RIGHT(TEXT(ABS(B2/24),"s.00"),2)&IF(B2>=0,"N","S")
And in cell C3, enter the following formula:
=TEXT(ABS(B3/24),"[hh],mm.ss")&RIGHT(TEXT(ABS(B3/24),"s.00"),2)&IF(B3>=0,"E","W")
And that's it.
The two formulae are basically the same: three calculations, converted to text and strung together using the & operator. They work using the absolute value of the decimal coordinate, divided by 24, meaning that if it is less than 0, it will not display the negative sign.
- The first formula displays the coordinate as hours, minutes and seconds in the necessary format for Adobe Bridge
- The second formula displays the coordinates as seconds and hundredths of a second, but takes only the two characters at the right hand side and bungs them on the end of the first formula.
- The third formula checks to see if the decimal figure is negative or not. If it is greater than 0 then display an "N" at the end of the coordinates, otherwise display an "S" (for latitude). For longitude, display an "E" or a "W".
To use the spreadsheet, go to Google Maps and find the location where you took your picture.
For example, I have a picture that was taken at Logie Kirk, near Stirling. I find it in Google Maps, click on the location where I was stood, and a bar comes up at the bottom of the screen showing the decimal coordinates:
I copy and paste the two coordinates into column B of the spreadsheet and the appropriate GPS coordinates for Adobe Bridge are shown in column C.
Comments
Post a Comment