# Issues with Converting from DMS to DD

#### mpratt

##### New Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Andrew Poulsom

##### MrExcel MVP
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)

#### mpratt

##### New Member
This is what I got after trying those equations:

LatitudeLongitudeLat_DegreeLong_Degree
37° 34' 45.90"-87° 39' 34.60"
 8.9627e+06

<tbody>
</tbody>
 -2.09744e+07

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
I copy and pasted the equations, and just changed the cell location.

#### Andrew Poulsom

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

#### mpratt

##### New Member
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

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

#### Rick Rothstein

##### MrExcel MVP
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_

##### Well-known Member
the usual separator issues:

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

Last edited:

#### mpratt

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

373413.8

#### mpratt

##### New Member
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?

Replies
0
Views
143
Replies
0
Views
758
Replies
1
Views
940
Replies
2
Views
5K
Replies
1
Views
350

Threads
1,195,679
Messages
6,011,119
Members
441,585
Latest member
MargaretBartley

### 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

### 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