Solved

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

Posted on 2012-03-31
8
270 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 41

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 41

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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 41

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 41

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now