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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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,
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" 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>