Convert latitudes and longitudes to decimal


G

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
 
Ad

Advertisements

C

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
 
G

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.
 
G

Guest

Sorry ..

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

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
 
G

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!
 
Ad

Advertisements

C

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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top