# 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

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

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!

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

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