Concerting Degrees, Minutes, Seconds to Decimal Degress Using A Function or Macro in Excel

Hi,

I have tabular data in degrees, minutes, seconds that I need to convert to decimal degrees in order to import the data into a GIS.

I has an Experts Exchange programmer help me with a function several months ago but I can't figure out how to apply the function to the attached spreadsheet.  I'd also like to know whether or not it is possible to apply the function to a different data set with the same problem.  I think I need to some how run the function but I don't know how to do it.

I have attached the sample data and the code here.

I would really appreciate any guidance.

Best,
Kannika
GPS-Villages-Macro2.xlsx
kannikaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
First, the function must be placed in a public module.  hit ALT-F11, look to the left, find VBProject(your workbook name) then right click that and INSERT->Module, then you can paste the code there.

In the worksheet, you have to use that function like:

[C2]=decdeg(B2)

And copy down.  Also,

[E2]=decdeg(D2)

Finally, your format of how you enter the Northing and Easting data in the worksheet must have changed since you had this function developed.  I suspect from looking at the code that you used to enter it as:

degrees (space) minutes (space) seconds (space)

Now, you're entering it as:

degrees.minutes'seconds" (using the funny single and double quote characters).

Here's your revised code leveraging your new data entry approach.  I tested the result versus an online calculator and this function is correct 100%:
Public Function DecDeg(cl As Range)
    Dim pos1 As Long, post2 As Long
    Dim deg As Double, min As Double, sec As Double

    pos1 = InStr(1, cl.Value, ".")
    pos2 = InStr(pos1 + 1, cl.Value, Chr(146)) 'dipthong character
    pos3 = InStr(pos2 + 1, cl.Value, Chr(148)) 'right quotation character
    
    deg = Val(Mid(cl.Value, 1, pos1 - 1))
    min = Val(Mid(cl.Value, pos1 + 1, pos2 - 1))
    sec = Val(Mid(cl.Value, pos2 + 1, pos3 - 1))
    
    DecDeg = deg + Val(min & Format(sec / 60, ".0000")) / 60
End Function

Open in new window


See attached.

Enjoy!

Dave
GPS-Villages-Macro2.xlsm
0
kannikaAuthor Commented:
Thanks, Dave!  The code works perfect for the data set that used a period.

However, I also have some data sets with a degree symbol that I have to convert.  When I tried to convert your code to use a degree symbol it wouldn't work. I also changed the pos1+1 and pos2+1 to pos1+2 and pos2+2 because of the space between values.

I'm not sure if that's because there is a conflict with the o name of the function (both are named DecDeg). I tried to rename the second function DecDeg1 but that didn't work.

What do I need to do to the sample data excel file and code to get it to work with the data that has a degree sign?

I have attached the other data set that uses a degree sign instead of a period. The data set has several fields. The field with the degrees, minutes, seconds data is called DA_BenchmarkLongitude and DA_BenchmarkLatitude.

Again, thanks for your help on this!

thanks
Kannika
DataSample-Degree-sign.xlsm
0
dlmilleCommented:
Ok - I fixed it to work with this change in format.  Your problem was that the dipthong was replaced with a grave symbol, so ( ' ) was the minutes and then the last was a regular quote.

You need to just ensure consistency of data format.  If its not working, then find/replace the symbols to be those that align with the function.

See attached.  I assume this completes this project, correct?

Dave
DataSample-Degree-sign-r1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Saqib Husain, SyedEngineerCommented:
Try this formula

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

For your second file you can use

=LEFT(Z3,FIND("°",Z3)-1)+MID(Z3,FIND("°",Z3)+1,FIND("'",Z3)-FIND("°",Z3)-1)/60+MID(Z3,FIND("'",Z3)+1,FIND("""",Z3)-FIND("'",Z3)-1)/3600

The problem with the second file is that in some locations it has '' (two quotes) instead of " (one double quote) and in other locations it has char(160) instead of a space.

You will have to correct those inconsistencies before applying any method.
0
dlmilleCommented:
@ssaqibh - I don't see the value in using formulas for this - also your conversions are incorrect.  However,If the format ever changes again, there's only one place to change it with a VBA function that the OP understands, however, many formulas would have to be changed with the embedded formulas and is a more complex operation at that.

@kannika - The key issue is to understand the change in characters depending on dataset and whether/how many spaces are added before/after the marks and you have done a good job adjusting the function to address that - just need to ensure that you identify the correct characters for degrees, minutes & seconds when the dataset changes on you.  If a conversion doesn't work its likely a space issue or the need to reformat that reference if the degrees, minutes or seconds character is slightly different.

Dave
0
kannikaAuthor Commented:
Best service I could possibly want! Thanks Dave! You saved the day!

Lyndy
0
Saqib Husain, SyedEngineerCommented:
Dave,

I don't see the value in using formulas for this
The values of formulas can not be overemphasized. The basic argument is that it avoids any VBA and remains within native excel. But of course one can choose any.

- also your conversions are incorrect
I don't see why you say that. The difference between yours any mine is only 5.55555555337151E-07 which I believe is only precision related. But talking about correctness I find that using your routine the conversion for cell AA9 is incorrectly done because the seconds exceed 60. My formula evaluates it correctly.

However,If the format ever changes again, there's only one place to change it...
I agree.

cheers

Saqib
0
dlmilleCommented:
@Saqib - I must have pasted your formula incorrectly - MUCH apologies!  See the attached, however, it appears that your formula tested on row 10 demonstrates a flaw.

I personally don't believe in native formulas at all costs if they cannot be interpreted/debugged very quickly.  Especially, if there are a low relative number of function calls.  If there are thousands, I would yield to the formulaic approach as well in most cases, agreed.  not all native formulas are guaranteed to beat out VBA by the way, but that's a bit off topic.

@kannika - please note update...

The VBA function should be written as:

Public Function DecDeg(cl As Range)
    Dim pos1 As Long, post2 As Long
    Dim deg As Double, min As Double, sec As Double
            
    pos1 = InStr(1, cl.Value, "°") 'degree character
    pos2 = InStr(pos1 + 2, cl.Value, "'") 'dipthong character
    pos3 = InStr(pos2 + 2, cl.Value, """") 'right quotation character
                
    deg = Val(Mid(cl.Value, 1, pos1 - 1))
    min = Val(Mid(cl.Value, pos1 + 1, pos2 - 1))
    sec = Val(Mid(cl.Value, pos2 + 1, pos3 - 1))
                
    'DecDeg = deg + Val(min & Format(sec / 60, ".0000")) / 60
    DecDeg = deg + min / 60 + sec / 3600
End Function

Open in new window


See attached.

Dave
DataSample-Degree-sign-r2.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.