Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.