Convert GPS Degrees Minutes Seconds to Decimal

**Nelson.B22** · 02-03-2014, 03:13 PM

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.

**popipipo** · 02-03-2014, 03:20 PM

Take a look at this dutch document

**Nelson.B22** · 02-03-2014, 03:36 PM

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.

**popipipo** · 02-03-2014, 03:45 PM

If you post a xls file here I try to translate it

**Nelson.B22** · 02-03-2014, 03:52 PM

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.

**popipipo** · 02-03-2014, 04:20 PM

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

**Nelson.B22** · 02-03-2014, 05:00 PM

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.

**popipipo** · 02-03-2014, 05:38 PM

Extra columns is that allowed?
It makes the formulas much easier.

**Nelson.B22** · 02-03-2014, 05:45 PM

Yeah, let's say A1 has the value: 12°34'56.78"N, let's make B1 cell the decimal version of A1

Same here, let's say A2 has the value: 12.345678°, let's make B2 cell the decimal version of A2

I'm 99% that each formula is going to be different, and again, I'll take care of the pasting stuff for each format.

Thanks again.

BTW, I thought I found the solution here: http://shaileshchaure.com/howconverDminuts_Ddecimal.php by using the formula described under "COMPLICATED BUT BETTER WAY":

=LEFT(B23, FIND("°",B23,1)-1)+(MID(B23,FIND("°",B23,1)+1,(FIND("’",B23,1)-FIND("°",B23,1))-1)/60)+(MID(B23,FIND("’",B23,1)+1,(FIND("”",B23,1)-FIND("’",B23,1))-1)/3600)

But no luck for me

--Nelson.

**shg** · 02-03-2014, 06:10 PM

Assuming you always have seconds to hundredths,

	A	B
1	12°34'56.78"N	12.58244

In B1:

=IF(OR(RIGHT(A1)={"S","W"}), -24, 24) * TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1, LEN(A1)-1), "°", ""), "'", ""), """", ""), " ", ""), "0\:00\:00.00")

**Nelson.B22** · 02-03-2014, 06:28 PM

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.

**shg** · 02-03-2014, 06:51 PM

It needs two-digit minutes.

**Nelson.B22** · 02-03-2014, 07:07 PM

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.

**shg** · 02-03-2014, 07:32 PM

You could use a UDF:

	A	B	C
1	12°34'56.78"S	-12.5824	B1: =DMS2Deg(A1)
2	12°3'45.67"N	12.06269

Function DMS2Deg(ByVal s As String) As Double
    Dim iSgn        As Long
    ' Converts dd° mm' ss.ss{N|E|S|W}" to decimal degrees

    s = Replace(s, " ", "")
    Select Case UCase(Right(s, 1))
        Case "N", "E"
            iSgn = 1
            s = Left(s, Len(s) - 1)
        Case "S", "W"
            iSgn = -1
            s = Left(s, Len(s) - 1)
    End Select

    s = Replace(s, "°", "+")        ' replace the degree sign
    s = Replace(s, "'", "/60+")     ' replace the minute sign
    s = Replace(s, """", "/3600")   ' replace the second sign (double quote)
    s = Replace(s, "''", "/3600")   ' replace the second sign (two single quotes)
    If Left(s, 1) = "-" Then
        DMS2Deg = -iSgn * Evaluate(Mid(s, 2))
    Else
        DMS2Deg = iSgn * Evaluate(s)
    End If
End Function

**Nelson.B22** · 02-03-2014, 07:53 PM

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.

**shg** · 02-03-2014, 08:01 PM

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

**Nelson.B22** · 02-03-2014, 08:09 PM

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:

=--SUBSTITUTE(G4, "°", "")

and

=SUBSTITUTE(G4, "°", "")

?

I really appreciate it.

--Nelson.

**shg** · 02-03-2014, 08:21 PM

Make the column containing the formula wide, set the horizontal alignment to General, and try it both ways. What do you see?

**Nelson.B22** · 02-03-2014, 08:28 PM

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.

**shg** · 02-03-2014, 08:38 PM

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

**Nelson.B22** · 02-03-2014, 09:03 PM

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.

**shg** · 02-03-2014, 09:15 PM

That is the key difference, and a glaring clue that people lose when they center everything, and then can't figure out why their formulas don't work.

MANY functions ignore text that looks like numbers.

**Nelson.B22** · 02-03-2014, 11:39 PM

I see. Interesting. Thanks for pointing that out.

--Nelson.

Convert GPS Degrees Minutes Seconds to Decimal

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Re: Convert GPS Degrees Minutes Seconds to Decimal

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] convert decimal degrees to degrees minutes seconds

[SOLVED] Decimal Degrees to Degree Minutes Seconds Conversion VBA Stripping Leading Zeros

Converting Lat/Long (Degrees Minutes Seconds) to Decimal Degrees

how can i convert degrees/minutes/seconds to decimal degrees?

How do i convert angles from degrees,minutes, seconds to decimal

Bookmarks

Bookmarks

Posting Permissions