Convert decimal to degrees, minutes, seconds

Finally got smart and bought a Mac and now I'm in the progress of re-learning things. Hopefully someone out there can help me with this one. I create data bases for GPS' and I'm wanting to start using Numbers as my spreadsheet to build these databases. Does anyone know how to create a formula that will convert a coordinate that is a decimal to degrees, minutes, second and visa versa. When building a spreadsheet I have to do this twice on every line and the one I'm working on now has over 10,000 lines. What I would really like to do is be able to enter the coordinates I have and let the spreadsheet convert the second one. When working with data sometimes I have decimal coordinates to work with and other times I have degree, minutes and seconds so I need to be able to convert both directions. Any help you can give me is greatly appreciated because I would sure like to get this project done so I can go enjoy my new BMW motorcycle. Thanks, Bob

MacBook Pro 17", Mac OS X (10.5.2)

Posted on Mar 12, 2008 9:26 PM

22 replies

Oisin

Level 1

8 points

May 31, 2008 10:34 AM in response to pw1840

Hi again,
yes I did place it in the immediate right column, but still getting the red triangle with SEARCH not finding "º". I change all the cell references to G34 - where the 1st formula outputs to. Is this the error? Do I only need to change A2 to G34 and A to G's?
Cheers

Oisin

Level 1

8 points

May 31, 2008 10:49 AM in response to Oisin

Yes - success. I was just cutting and pasting - when I paste and match style it worked!!!!

Thanks very much for this help it is really appreciated!

pw1840

Level 3

860 points

May 31, 2008 11:44 AM in response to Oisin

Olsin,

Isn't it a great feeling when you get something complicated like this to work? But I don't understand why using "Paste and Match Style" is the solution. I was able to duplicate your problem and discovered that if the degree symbol (opt-0) was re-typed in the first formula everything worked perfectly. Unless someone can offer an explanation as to how match style or how retyping opt-0 makes this work, I guess I'll have to put it in my "mysteries" file for now.

However, I'm certainly glad you're satisfied with the results.

pw

pw1840

Level 3

860 points

May 31, 2008 12:19 PM in response to Oisin

Olsin,

I tried to edit my last response, but I guess I wasn't fast enough.

After sending that message, further investigation found that the unicode for the degree symbol used in the the two formulas was different. This is why you got the search error. The reason they are different is first I took Yvan's formula and modified it to go from decimal to deg/min/sec. Then in developing the reverse formula, I used opt-0 for the degree symbol not realizing this code was different from the one used by Yvan.

However, I still don't understand why using "Paste and Match Style" is a solution.

pw

KOENIG Yvan

Level 8

41,819 points

May 31, 2008 12:41 PM in response to pw1840

Hello

your º is the character whose Unicode is 186
When I enter a degree on a french keyboard, I type shift + ) and the resulting character is ° whose Unicode is 176.

You kept some usage of A2 in lieu of the short A
You kept an useless ABS
You used *(-1) when a simple - was OK

After these changes, I was at least able to get a running formula, this one:

=IF(ISBLANK(A),"",IF(LEFT(A,1)="-",-(ABS(VALUE(LEFT(A,SEARCH("°",A)-1))) VALUE(MID(A,SEARCH("°",A)+1,2))/60+VALUE(MID(A,SEARCH("'",A)+1,2))/3600),VALUE(L EFT(A,SEARCH("°",A)-1))+VALUE(MID(A,SEARCH("°",A)+1,2))/60+VALUE(MID(A,SEARCH("' ",A)1,2))/3600))

It took time to discover that to mark seconds you used twice the character used for minutes.
I am accustomed to use the double quote and so the formula returned an error message.

Yvan KOENIG (from FRANCE samedi 31 mai 2008 21:41:11)

pw1840

Level 3

860 points

May 31, 2008 9:06 PM in response to KOENIG Yvan

Yvan,

Thank you for the critique of the formula I offered for converting deg/min/sec to decimal. As usual, your comments were on the mark and I appreciate your taking the time to review the formula and respond.

Incidentally, there was a comment with my original posting of that formula to indicate double apostrophes were used in place of single quotes, but that was a note easy to overlook. Perhaps using CHAR(34) would have been more appropriate.

Once again, thanks,

pw

KOENIG Yvan

Level 8

41,819 points

Jun 1, 2008 12:56 AM in response to pw1840

Hello

It's not because I'm accustomed to use a double quote that it is a better choice.
I wrote that because if I used this char, we may assume that other users made the same choice.
What is more important, is the existence of two characters used to represent degree.
On a French keyboard, the one which you uses is reached thru alt + u while the one I uses is reached by shift + ) which is the more often used here.
But it's not an anomaly of your script.
It's just a sample of localization problems.

Now, I'm aware of that and I will try to remember to be precise if I uses the degree character in a formula 😉

Don't worry, I was not criticizing, I was just pointing possible enhancements.
I hope that you read it as a positive comment.
Only the embedded A2 was a possible source of problems if the formula was not entered in row2.

Yvan KOENIG (from FRANCE dimanche 1 juin 2008 09:55:17)

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Convert decimal to degrees, minutes, seconds