Convert Degree Minute Second to Decimal in excel (DMS to D.ddd)

this tutorial is for surveyors.
how to convert a Bearing or Latitude Longitude from Degree Mininute Second format to decimal format
bearing Calculation in surveying
SUBSCRIBE us (it's FREE)
Like Share & Support.
With best wishes
L&E Team!

[@lazolabooi8212]

This is an excellent workflow. Thank you very much.

[@anushmalifestyles3466]

Thank you very much for a valuable idea.

[@vargapeter38]

Thank you! It helped a lot!

[@tutorialpalace-civilengine2593]

I love this video and I also want to see how to convert DMS into bearing?( reverse process)

[@NimishP]

Let's say you've decimal degree in cell A1. Then write formulas as follows -
Cell B1 =INT(A1)
This will isolate degree
Cell C1 =60*(A1-B1)
A1 - B1 will find decimal portion of degree which is converted to minute by multiplying it by 60
Cell D1 =INT(C1)
This will isolate minutes.
Cell E1 =ROUND(60*(C1-D1),0)
C1 - D1 will calculate decimal portion of minute which is converted to seconds by multiplying it by 60 and rounded off to zero decimal place
CELL F1 =B1&"°"&D1&"'"&E1&""""
This will put all things together
You can combine all formulas into one cell instead of having so many intermediate cells, but that needs fair bit of function nesting.

[@india2vijay]

Great work... Helped me. Thanks

[@sandrazaki9508]

thank you, it was really helpful

[@LearnExplore]

Thank you

[@NimishP]

Have a look at the following formula. This is very complicated and convoluted if you are new to nested functions, but, it works. Here C3 is the cell containing values in DMS that is required to be converted to decimals.
The first portions VALUE(LEFT(C3,FIND("°",C3)-1)) isolates degrees
The second portion VALUE(MID(C3,FIND("°",C3)+1,FIND("'",C3)-FIND("°",C3)-1))/60 isolates minutes and converts to degrees
The third portion VALUE(MID(C3,FIND("'",C3)+1,LEN(C3)-FIND("'",C3)-1))/3600 isolates seconds and converts to degrees
And then all three are added together.
=VALUE(LEFT(C3,FIND("°",C3)-1))+VALUE(MID(C3,FIND("°",C3)+1,FIND("'",C3)-FIND("°",C3)-1))/60+VALUE(MID(C3,FIND("'",C3)+1,LEN(C3)-FIND("'",C3)-1))/3600
Use Evaluate Formula to step through each stage.

[@LearnExplore]

Excellent brother. I learned new things from you. Thank you🙏

[@NimishP]

@@LearnExplore You are welcome. Your approach is equally good. The intent of using formula was to keep original data intact.
Thanks for the kind words.

[@hmoobroobntseej3369]

Thank you for your video

[@sardarananda007]

Good

[@LearnExplore]

Thanks dada

[@rajkpandey8969]

Thank you brother..

[@LearnExplore]

No worries

[@nehajaiswal5340]

if i am having a range of lat lon for example 22° 15' to 22°58' north latitude , then which value I should consider? kindly reply soon

[@NimishP]

Can you elaborate further. I am willing to help you out, but, could not understand the question.

[@sanelekhumalo9912]

But it doesn't give a hint to that question I asked, even though it helping a bit. Now can somebody please elaborate how to convert from SMS to DDD.ddd? Please guys

[@SharifulIslam-fp4yk]

You can easily convert degree minute second values into decimal degrees value in Microsoft Excel instantly. The length and amount of data is not a problem at all. You can even convert degree-minute-second readings with garbage into decimal degrees within just a matter of click. See the video- ruclips.net/video/2uVkE_mReBM/видео.html

[@massaplo7405]

I request the script in the bag ... can be sent in the comments column

[@LearnExplore]

I didn't understand what u told