Issues with Converting from DMS to DD

mpratt

New Member
Joined
Sep 5, 2014
Messages
16
I have a couple hundred Lat/Long coordinates to convert to decimal degree in this DMS format:

LatitudeLongitude
37° 34' 45.90"-87° 39' 34.60"

<tbody>
</tbody>

<tbody>
</tbody>
I've tried various VBA codes via help sites and a few equations but nothing I've tried has worked. I'm not the most adept at Excel, which may be part of the problem, but I simply don't know what else to do to convert these coordinates.

I'd appreciate any help at all.

Thanks!
-Maddy
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to MrExcel.

Does this help you?


Excel 2010
AB
1LatitudeLongitude
237 34' 45.90"-87 39' 34.60"
337.57941667-87.65961111
Sheet1
Cell Formulas
RangeFormula
A3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"° ",":"),"' ",":"),"""","")*24*IF(LEFT(A2,1)="-",-1,1)
B3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-",""),"° ",":"),"' ",":"),"""","")*24*IF(LEFT(B2,1)="-",-1,1)
 
Upvote 0
This is what I got after trying those equations:

LatitudeLongitudeLat_DegreeLong_Degree
37° 34' 45.90"-87° 39' 34.60"
8962701.6

<tbody>
</tbody>
-20974430.4

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
I copy and pasted the equations, and just changed the cell location.
 
Upvote 0
I used what you provided. However, I have my Lat and Long cells formatted via the Number catalog box to display the dms instead of having to type it in every cell. Would that impact the equation result?
 
Upvote 0
Yes of course. If your entry is a serial time you can just multiply it by 24 to return decimal degrees.
 
Upvote 0
I used what you provided. However, I have my Lat and Long cells formatted via the Number catalog box to display the dms instead of having to type it in every cell. Would that impact the equation result?
Most definitely. What value is shown in the Formula Bar when you select the cell containing "37° 34' 45.90"?
 
Upvote 0
the usual separator issues:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;"-";"");"° ";":");"' ";":");"""";"");".";",")*24*SIGN(LEFT(A2;2))
 
Last edited:
Upvote 0
the usual separator issues:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;"° ";":");"' ";":");"""";"");".";",")*24*SIGN(LEFT(A2;2))

When I tried this it said there was an error with the equation and highlighted the cell location. *I did change it to correspond to my sheet. So something is wrong in that cell that I have my Latitude entered into?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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