Convert latitudes and longitudes to decimal
 

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

Convert latitudes and longitudes to decimal
 

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

Sorry ..

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

"Toppers" wrote:

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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

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


"Toppers" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

Chip,
It only works if you ignore i.e. don't include, the < " Won
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" wrote:

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


"Toppers" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

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


"Toppers" wrote in message
...
Chip,
It only works if you ignore i.e. don't include,
the < " Won
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" wrote:

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


"Toppers" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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

Convert latitudes and longitudes to decimal
 

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

"Chip Pearson" wrote:

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


"Toppers" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Demi" wrote in message
...
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