Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
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
I don't see the value in using formulas for thisThe 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 incorrectI 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.
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Quarter Increment Clock | 2 | 15 | |
Excel VBA User Form Help | 21 | 28 | |
VBA: copy range dynamically based on config sheet v2 | 3 | 31 | |
Turn several entries on single cell, into individual lines | 14 | 26 |
Join the community of 500,000 technology professionals and ask your questions.