Issues with Converting from DMS to DD

[mpratt]

I have a couple hundred Lat/Long coordinates to convert to decimal degree in this DMS format:

Latitude Longitude 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

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this help you?

Excel 2010
	A	B
1	Latitude	Longitude
2	37 34' 45.90"	-87 39' 34.60"
3	37.57941667	-87.65961111
Sheet1

Cell Formulas
Range	Formula
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)

 

[mpratt]

This is what I got after trying those equations:

Latitude Longitude Lat_Degree Long_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.

 

[Andrew Poulsom]

You can see form the sample I posted that my formulas are working. What formulas are you using?

 

[mpratt]

ADVERTISEMENT

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?

 

[Andrew Poulsom]

Yes of course. If your entry is a serial time you can just multiply it by 24 to return decimal degrees.

 

[Rick Rothstein]

ADVERTISEMENT

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"?

 

[snb_]

the usual separator issues:

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

 

[mpratt]

Most definitely. What value is shown in the Formula Bar when you select the cell containing "37° 34' 45.90"?

373413.8

 

[mpratt]

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?