Solved

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

Posted on 2012-03-31
8
274 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:kannika
  • 4
  • 2
  • 2
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37791271
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
 

Author Comment

by:kannika
ID: 37792400
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37792434
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37792436
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37792455
@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
 

Author Closing Comment

by:kannika
ID: 37792482
Best service I could possibly want! Thanks Dave! You saved the day!

Lyndy
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37792506
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37792523
@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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question