Convert GPS Degrees Minutes Seconds to Decimal

Hi all,

This may have been already answered somewhere here in this forum, although I couldn't find it at least in the same format as I have my data.

I already tried what's explained on this video http://www.youtube.com/watch?v=0QxxuSIFdFU via VBA but the returned decimal values are not correct.


What would be the formula to convert the following data (it is in the same cell):

12°34'56.78"N --> to decimal (the only variation here would be the other coord.. instead of N it would be a W)

And:

12.345678° --> to decimal


Thanks in advance.

--Nelson.

Thank you popipipo.

As far as I can understand the document, it seems like the degrees, minutes and/or seconds are in separate cells, and that wouldn't work for me unfortunately.

Did I miss something there?

Thanks again.

--Nelson.

I don't think I'm following you, sorry.

Here's what I have:

A1 cell contains:

12°34'56.78"N ---> need to convert that to decimal

Another cell, let's say A2 contains:

12°34'56.78"W ---> need to convert that to decimal

Another cell, let's say B1 contains:

12.345678° ---> need to convert that to decimal

My entire xls file would look like the above.

Thanks,

--Nelson.

Originally Posted by popipipo

If A1 cell contains 12°34'56.78"N is this allways that format and A1 never contains 12.345678°

Yes, each cell contain either the format 12°34'56.78"N or 12.345678°, I just need to formula to convert each of these formats to decimal.

[I'll take care of pasting the formula respectively, that's not a problem].


Thanks,


--Nelson.

shg:

You nailed it! Although, one of my other values is like this:

12°3'45.67"N --> and it's decimal value is way off (somewhere in the Pacific Ocean )

Is it because of the Minutes are not to hundredths in this case?

Thank you.

--Nelson.

Originally Posted by shg

It needs two-digit minutes.

So is this an "impossible mission"?

Would it be better to separate Degrees, Minutes and Seconds in columns? (yuck!)

How can this be accomplished?

--Nelson.

Mr./Mrs. shg,

You have done it. So far all the tests done are accurate.
I really appreciate your help and time on this.

May I abuse of your knowledge and ask you about the other value: 12.345678° --> to decimal ?

I'm more or less familiar with other programming languages, I just can't wrap my head around VBA

Thanks so much.

--Nelson.

Originally Posted by shg

=--substitute(a1, "°", "")

Oh, duh, silly me.. I didn't realize that 12.345678° is the same as 12.345678 (sorry about that)... I thought it needed some kind of conversion from "degrees".

Anyway... if you don't mind, what's the difference between:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

?

I really appreciate it.

--Nelson.

Oh, so it is just for alignment..

With =--SUBSTITUTE(G4, "°", "") --> value is aligned to the right

With =SUBSTITUTE(G4, "°", "") --> value is aligned to the left

Ok.. thank you again for all your help. I'll mark this post as SOLVED!

--Nelson.

Originally Posted by shg

It's more than alignment. What does alignment tell you about the contents of a cell?

Hmmm... the difference between plain text and numerical values (?)

--Nelson.

I see. Interesting. Thanks for pointing that out.

--Nelson.