Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
Join the community of 500,000 technology professionals and ask your questions.