DMS to Decimal Degree

M

MJL5043

New Member
Joined
Oct 22, 2014
Messages
22
I have read several posts on here relating to this topic but for some reason I have been unable to get the formulas provided in the other posts to work for the particular format I am working with. I guess I am just mentally seized up today. I apologize for having to repost a topic that has already been gone over several time. The format I am working with is 41°14'36" -76°51'46" and I need to have it report in 41.24326 -76.86277 The Lat will always be positive and the Long will always be negative. I would like to have this done in a single cell and not split up over 3. Thanks
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Row\Col
A​
B​
C​
1​
41°14'36"
41.24333​
B1: =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"°", " "), "'", " "), """",)), " ", ":"), "-", ) * IF(LEFT(A1) = "-",- 24, 24)
2​
-76°51'46"
-76.86278​

I have no suggestion for having both values in a single cell, other than to use a UDF.
 
Upvote 0
I did not mean having both in a single cell, was trying to indicate one cell for lat one for long. Sorry for the confusion. Also I have the format on the DMS cells set to add in the ° ' " via (00"°"00"'"00\") and auto round the seconds to no decimal place. Is there a way to incorporate that into the formula? The way it is with the one you provided it is treating the cell as 411435.76 rather than 41°14'36"
 
Upvote 0
How does the cell value appear in the formula bar?

The way it is with the one you provided it is treating the cell as 411435.76 rather than 41°14'36"
Click to expand...
I don't think so:

Row\Col
B​
C​
5​
41.24333333​
B5: =41 + 14/60 + 36/3600
6​
41.24326667​
B6: =41 + 14/60 + 35.76/3600
 
Last edited:
Upvote 0
MJL5043 said:
Also I have the format on the DMS cells set to add in the ° ' " via (00"°"00"'"00\") and auto round the seconds to no decimal place.
Click to expand...
If you have the format set to display your values that way, then your cells probably already have the value in them that you want... what happens if you change the cells format to "General"? If that does not work, then select one of your cells and tell us what you see in both the cell and the Formula Bar.
 
Upvote 0
Switching the format to general displays the value as ( 411435.76) in both the cell and the formula bar. With the cell set to the custom format it displays ( 41°14'36") in the cell and in the formula bar it shows as (411435.76)
 
Upvote 0
Next attempt:

Row\Col
A​
B​
C​
1​
41°14'36"​
41.24333​
B1: =TEXT(SUBSTITUTE(A1, "-", ""), "0\:00\:00") * IF(LEFT(A1)="-", -24, 24)
2​
Format of A1: 0°00'00\"
3​
411435.76​
B3: =A1
 
Upvote 0
That works great for the Lat. When I use that for the Long though it switched it from negative to positive. (-76°51'46") to (76.86278)
 
Upvote 0
That doesn't round to the nearest second ...
 
Upvote 0
Was able to change the formatting of the Long cell to have it put the "-" back in. Thanks for all the help
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Changing DMS into decimal degrees
Replies
2
Views
239
kevin9999
K
J
  • Question
Excel VBA .Save Causing a Save As to 'Random' number filename
Replies
2
Views
150
jcoeng
J
N
  • Question
VLOOKUP acting weird
2
Replies
17
Views
377
Alex Blakenburg
A
G
  • Solved
SUMIFS - what am I missing
Replies
4
Views
102
Peter_SSs
Peter_SSs
B
  • Question
Conversion: Degrees to Deg-Min-Sec
Replies
2
Views
959
BillSaffian
B

Forum statistics

Threads
1,214,646
Messages
6,120,716
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top