Prev: Formulas that recognise data from two worksheets
Next: column corresponding to row number
From: Al on 18 Mar 2010 04:05
Hi All,

I have got a worksheet with cells like:

E174 36.992

I need to get that into decimal degrees like

E174.61653

Is there a way to do this with a forumla? I start with find, left,
right functions, but I ended up parenthesis hell.

Cheers

-Al
From: Joe User on 18 Mar 2010 04:30
"Al" <bigal.nz(a)gmail.com> wrote:
> I have got a worksheet with cells like:
> E174 36.992
> I need to get that into decimal degrees like
> E174.61653

One way:

=LEFT(A1,FIND(" ",A1)-1) &
TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))/60,".00000")


----- original message -----

"Al" <bigal.nz(a)gmail.com> wrote in message
news:86fa546d-9bb3-4b4b-9b76-c4fa6314adf6(a)h35g2000pri.googlegroups.com...
> Hi All,
>
> I have got a worksheet with cells like:
>
> E174 36.992
>
> I need to get that into decimal degrees like
>
> E174.61653
>
> Is there a way to do this with a forumla? I start with find, left,
> right functions, but I ended up parenthesis hell.
>
> Cheers
>
> -Al

From: "David Biddulph" groups [at] on 18 Mar 2010 04:30
=LEFT(A2)&TEXT(MID(A2,2,FIND(" ",A2)-2)+RIGHT(A2,LEN(A2)-FIND("
",A2))/60,"#.00000")
--
David Biddulph


"Al" <bigal.nz(a)gmail.com> wrote in message
news:86fa546d-9bb3-4b4b-9b76-c4fa6314adf6(a)h35g2000pri.googlegroups.com...
> Hi All,
>
> I have got a worksheet with cells like:
>
> E174 36.992
>
> I need to get that into decimal degrees like
>
> E174.61653
>
> Is there a way to do this with a forumla? I start with find, left,
> right functions, but I ended up parenthesis hell.
>
> Cheers
>
> -Al


From: Ron Rosenfeld on 18 Mar 2010 06:22
On Thu, 18 Mar 2010 01:05:18 -0700 (PDT), Al <bigal.nz(a)gmail.com> wrote:

>Hi All,
>
>I have got a worksheet with cells like:
>
>E174 36.992
>
>I need to get that into decimal degrees like
>
>E174.61653
>
>Is there a way to do this with a forumla? I start with find, left,
>right functions, but I ended up parenthesis hell.
>
>Cheers
>
>-Al

=MID(A1,2,FIND(" ",A1)-2)+MID(A1,FIND(" ",A1)+1,99)/60

--ron
 | 
Pages: 1
Prev: Formulas that recognise data from two worksheets
Next: column corresponding to row number