Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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
0
kannika
Asked:
kannika
  • 4
  • 2
  • 2
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Independent Software Vendors: 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!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now