Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-31
8
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Industry Leaders: 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

704 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