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

 

[mpratt]

After taking a break from this, I think I'm ready to try again. Hopefully this is the last issue I have with this formula. The issue I am having now is with this formula:

=LEFT(J12,LEN(J12)-6)+24*SIGN(J12)*RIGHT(TEXT(ABS(J12),"0\:00\:00.0"),7)

For some reason, it is reporting a #VALUE! error when given a Lat. or Long. with zeros after the decimal in this format:
37°35'14.00"

-or-

-87°44'56.00"

Could someone please help me figure out what is the issue with the formula?
Thanks!

 

[Rick Rothstein]

After taking a break from this, I think I'm ready to try again. Hopefully this is the last issue I have with this formula. The issue I am having now is with this formula:

=LEFT(J12,LEN(J12)-6)+24*SIGN(J12)*RIGHT(TEXT(ABS(J12),"0\:00\:00.0"),7)

For some reason, it is reporting a #VALUE! error when given a Lat. or Long. with zeros after the decimal in this format:
37°35'14.00"

-or-

-87°44'56.00"

Could someone please help me figure out what is the issue with the formula?
Thanks!

See if this modified version of the formula I posted earlier works for you...

=LEFT(ROUNDDOWN(J12,0),LEN(ROUNDDOWN(J12,0))-4)+24*SIGN(J12)*RIGHT(TEXT(TEXT(ABS(J12),"0.0"),"0\:00\:00.0"),7)

 

[mpratt]

That worked!! Thank you so much for all your help!!!!!!!!!!