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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]37° 34' 45.90"[/TD]
[TD]-87° 39' 34.60"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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]

the usual separator issues:

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

Only if the decimal separator is the comma rather than the dot (and the list separator is the semicolon rather than the comma).

 

[Rick Rothstein]

373413.8

Give this formula a try...

=24*TEXT(A1, "0\:00\:00.0")

 

[Andrew Poulsom]

373413.8

So what number format are you using?

 

[mpratt]

Give this formula a try...

=24*TEXT(A1, "0\:00\:00.0")

I think that worked. I got 37.5705.

 

[mpratt]

However, it didn't work for my Longitude values. Again, it highlighted the cell with my Long value in it as the cause of the problem.

 

[mpratt]

So what number format are you using?

Custom: 00° 00' 00.00''

 

[Andrew Poulsom]

Custom: 00° 00' 00.00''

It would have been really helpful if you'd said that at the outset.

 

[Rick Rothstein]

However, it didn't work for my Longitude values. Again, it highlighted the cell with my Long value in it as the cause of the problem.

Describe "didn't work". Your "it highlighted the cell" comment made it sound like you got an error... if so, what was the error... and what was the value in the Formula Bar for that cell?

 

[Andrew Poulsom]

Probably needs:

=24*TEXT(ABS(A1), "0\:00\:00.0")*SIGN(A2)

 

[mpratt]

Probably needs:

=24*TEXT(ABS(A1), "0\:00\:00.0")*SIGN(A2)

That worked for most of the longitude values. I'm still getting an error message on a few of the cells however. I'm not sure what the error is though. For the cells with the error message this is what I know:

-Error Message for Long cells:

• Show Calculation steps > 24*TEXT(ABS(K13), "0\:00\:00.0")*Sign(K13) > Evaluate > 24*TEXT(ABS(-874685.7), "0\:00\:00.0")*Sign(K13) > Evaluate> 24*TEXT(874685.7, "0\:00\:00.0")*Sign(K13) >Evaluate> 24*"87:46:85.7"*Sign(K13) >Evaluate > #Value!*Sign(K13) >Evaluate> #Value!*Sign(-874685.7) >Evaluate> #Value!*-1 >Evaluate > #Value!

-Error Message for Lat cells:

• Show Calculation steps> 24*TEXT(J13), 0\:00\:00.0")> Evaluate> 24*TEXT(371788.7,"0\:00\:00.0") > Evaluate > 24*"37:17:88.7" (The next evaluation will show an error) > Evaluate > #Value!

-Formatting for cells: Custom- 00° 00' 00.00''
-Formula bar display for the cell: 371788.7 for latitude and -874685.7 for longitude

I'm not sure if this is helpful at all, but I don't understand what Excel is telling me is wrong.