Convert latitudes and longitudes to decimal

[Guest]

i have data in the form 90° 58' 12.00" W for latitudes and longitudes. The
data is huge and so I need to have a formula to convert this data in decimal
form like 90.xxxx .
I tried cpearson website but it asks me to enter the data in h:mm:ss form,
the problem is i already have the data as shown above and i would like to
know how to get it converted to decimals directly so that I can drag the
formula for the entire column.
Thanks

demi

 

[Chip Pearson]

Try

=(TIME(INT(LEFT(A1,2)),INT(MID(A1,5,2)),INT(RIGHT(A1,5)))+(INT(LEFT(A1,2)/24)))*24


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Having tried Chip's formula (and got an error), I believe it should be:

=(TIME(INT(LEFT(A1,2)),INT(MID(A1,5,2)),INT(MID(A1,9,2)))+(INT(LEFT(A1,2)/24)))*24


which 90.97 for your example.

 

[Guest]

Sorry ..

=(TIME(INT(LEFT(A1,2)),INT(MID(A1,5,2)),INT(MID(A1,9,5)))+(INT(LEFT(A1,2)/24)))*24

 

[Chip Pearson]

My formula works. It doesn't produce an error as long as A1
contains the specified string, including the embedded spaces. It
returns 90.97.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip,
It only works if you ignore i.e. don't include, the < " W>on
the end of the string (90° 58' 12.00" W).

I assumed this was part of the string and hence my change! We are both right!

 

[Chip Pearson]

Yeah, you're right. I assumed it was not part of the string.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thank you very much Chip Pearson and Toppers.

That is a lot of help. However, I am still trying to understand how exactly
this formula works.

Demi