Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: convert degree decimal minutes to decimal degrees in excel

1. I agreed to these rules
Join Date
7th July 2011
Posts
4

convert degree decimal minutes to decimal degrees in excel

Hi all, a new comer here, thanks for looking I hope the thread title is understandable but I'll explain - I have lots of data to convert and I'm using excel. The raw data came in an individual cell --- 41° 35.87' I was reading on other threads to convert with degrees, minutes and seconds, but I need degrees and decimal minutes I guess. I'm assuming the developer tool in excel will be needed as the data is one cell but I don't know what to program to write to convert all the cells into just degree decimal such as ----- 41.598 Thanks for looking and thanks in advance for any help that can be offered.

Excel Video Tutorials / Excel Dashboards Reports

2. I agreed to these rules
Join Date
7th July 2011
Posts
3

Re: convert degree decimal minutes to decimal degrees in excel

jamfoley,if the data is formatted the same as your example and the coordinates are in column A use the following formula:=ROUND(LEFT(A2,2)+RIGHT(A2,5)/60, 3)

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
7th July 2011
Posts
4

Re: convert degree decimal minutes to decimal degrees in excel

Thank you so much for the formula, it works a treat. I am now however met by another problem. My data came in Northings and then Eastings and Westings. I should have explained this in my first post but I didn't see it as a problem then. The data switches from West to East in the middle of the range of data and hence Excel cannot turn the positive West into negative East. Advice from anyone here would be of benefit. Kind regards,

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
7th July 2011
Posts
3

Re: convert degree decimal minutes to decimal degrees in excel

This formula should work with positive or negative coordinates:=IF(LEFT(A4,1)="-", ROUND(MID(A4,2,2)+MID(A4,6,5)/60,3)*-1,ROUND(LEFT(A4,2)+MID(A4,5,5)/60, 3))

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
7th July 2011
Posts
4

Re: convert degree decimal minutes to decimal degrees in excel

Hi, I am having trouble understanding the solution that was just posted. I input the formula to compute the data but did not attain the correct answers as I would achieve if I was using a calculator. I have attached a sample of the data below so maybe it could help to portray what I am trying to do. Thanks in advance and I appreciate all input. Thanks again

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
7th July 2011
Posts
3

Re: convert degree decimal minutes to decimal degrees in excel

This should work=IF(RIGHT(B2,1)="E",ROUND(LEFT(B2,2)+MID(B2,4,6)/60, 3)*-1, ROUND(LEFT(B2,2)+MID(B2,4,6)/60, 3))

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
7th July 2011
Posts
4

Re: convert degree decimal minutes to decimal degrees in excel

Mr Lenny, Thank you so much. That has worked a treat. Much appreciated. This forum is brilliant and I will be donating. Thank you to all for your help.

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
23rd May 2012
Posts
1

Re: convert degree decimal minutes to decimal degrees in excel

Hi all, a new comer here, I need ur help, really2 need. I have lots of data to convert and I'm using excel, I'm tired if i must convert it one by one with google earth .. need time too much to do it
The raw data came in an individual cell -> -7 22.042 and 108 12.867
I was reading on other threads to convert with degrees, minutes and seconds, but I need degrees and decimal minutes I guess. I'm assuming the developer tool in excel will be needed as the data is one cell but I don't know what to program to write to convert all the cells into just degree decimal such as -> -7.367367 and 108.214450
Thanks for looking and thanks in advance for any help that can be offered.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno