Degrees

[Michael M]

Hi All
I am trying to input Degrees/minutes/seconds in the one cell.
I have setup a custom format thus:
[h]�:mm':ss''
However, when I type in 017�30'20'' I get 4152480�:00':00''
What am I doing wrong.
All I really want to do is type in 0173020 as a number and have excel make
it look like 017�:30':20''

Any help would be appeciated
Regards
Michael M

[WLMPilot]

Michael,

I used CHAR() to obtain the format that you want. However, the way I
figured it out required you to input the number 0713020 (in this example) in
a cell and then have another cell pick up that number and format it like you
want it. If you copy and paste the formula below you will see what I mean.
In my example, have A1 be the cell you enter the number 0173020. Format A1
as TEXT. Then place the below formula in the cell you need to have it in
Deg/Min/Sec.

=IF(A1="","",LEFT(A1,3)&CHAR(186)&":"&MID(A1,4,2)&CHAR(146)&":"&RIGHT(A1,2)&CHAR(148))

Hope this works for you.
Les

"Michael M" wrote:

> Hi All
> I am trying to input Degrees/minutes/seconds in the one cell.
> I have setup a custom format thus:
> [h]°:mm':ss''
> However, when I type in 017°30'20'' I get 4152480°:00':00''
> What am I doing wrong.
> All I really want to do is type in 0173020 as a number and have excel make
> it look like 017°:30':20''
>
> Any help would be appeciated
> Regards
> Michael M
>

[Michael M]

Les
Thanks for the reponse. It is very close....but.
when I type in 0173020, I get 173º:02’:20”

It seems to be moving everything over a couple of places
I also tried 173020 with the same answer as above.
Regards
Michael M




"WLMPilot" wrote:

> Michael,
>
> I used CHAR() to obtain the format that you want. However, the way I
> figured it out required you to input the number 0713020 (in this example) in
> a cell and then have another cell pick up that number and format it like you
> want it. If you copy and paste the formula below you will see what I mean.
> In my example, have A1 be the cell you enter the number 0173020. Format A1
> as TEXT. Then place the below formula in the cell you need to have it in
> Deg/Min/Sec.
>
> =IF(A1="","",LEFT(A1,3)&CHAR(186)&":"&MID(A1,4,2)&CHAR(146)&":"&RIGHT(A1,2)&CHAR(148))
>
> Hope this works for you.
> Les
>
> "Michael M" wrote:
>
> > Hi All
> > I am trying to input Degrees/minutes/seconds in the one cell.
> > I have setup a custom format thus:
> > [h]°:mm':ss''
> > However, when I type in 017°30'20'' I get 4152480°:00':00''
> > What am I doing wrong.
> > All I really want to do is type in 0173020 as a number and have excel make
> > it look like 017°:30':20''
> >
> > Any help would be appeciated
> > Regards
> > Michael M
> >

[Michael M]

Hi
DUH !!!
I only followed half of your instructions, once I formatted A1 to text the
problem was solved.

Thanks for your help and quick response

Michael M

"WLMPilot" wrote:

> Michael,
>
> I used CHAR() to obtain the format that you want. However, the way I
> figured it out required you to input the number 0713020 (in this example) in
> a cell and then have another cell pick up that number and format it like you
> want it. If you copy and paste the formula below you will see what I mean.
> In my example, have A1 be the cell you enter the number 0173020. Format A1
> as TEXT. Then place the below formula in the cell you need to have it in
> Deg/Min/Sec.
>
> =IF(A1="","",LEFT(A1,3)&CHAR(186)&":"&MID(A1,4,2)&CHAR(146)&":"&RIGHT(A1,2)&CHAR(148))
>
> Hope this works for you.
> Les
>
> "Michael M" wrote:
>
> > Hi All
> > I am trying to input Degrees/minutes/seconds in the one cell.
> > I have setup a custom format thus:
> > [h]°:mm':ss''
> > However, when I type in 017°30'20'' I get 4152480°:00':00''
> > What am I doing wrong.
> > All I really want to do is type in 0173020 as a number and have excel make
> > it look like 017°:30':20''
> >
> > Any help would be appeciated
> > Regards
> > Michael M
> >

[WLMPilot]

Glad it worked out. Are you a pilot by chance? I am.

Les

"Michael M" wrote:

> Hi
> DUH !!!
> I only followed half of your instructions, once I formatted A1 to text the
> problem was solved.
>
> Thanks for your help and quick response
>
> Michael M
>
> "WLMPilot" wrote:
>
> > Michael,
> >
> > I used CHAR() to obtain the format that you want. However, the way I
> > figured it out required you to input the number 0713020 (in this example) in
> > a cell and then have another cell pick up that number and format it like you
> > want it. If you copy and paste the formula below you will see what I mean.
> > In my example, have A1 be the cell you enter the number 0173020. Format A1
> > as TEXT. Then place the below formula in the cell you need to have it in
> > Deg/Min/Sec.
> >
> > =IF(A1="","",LEFT(A1,3)&CHAR(186)&":"&MID(A1,4,2)&CHAR(146)&":"&RIGHT(A1,2)&CHAR(148))
> >
> > Hope this works for you.
> > Les
> >
> > "Michael M" wrote:
> >
> > > Hi All
> > > I am trying to input Degrees/minutes/seconds in the one cell.
> > > I have setup a custom format thus:
> > > [h]°:mm':ss''
> > > However, when I type in 017°30'20'' I get 4152480°:00':00''
> > > What am I doing wrong.
> > > All I really want to do is type in 0173020 as a number and have excel make
> > > it look like 017°:30':20''
> > >
> > > Any help would be appeciated
> > > Regards
> > > Michael M
> > >

[Michael M]

Nah, sorry.
I can pilot a bottle of Chardy and I can sometimes pilot a golf ball.
But I am just helping a surveyor out with this problem.

Regards
Michael M

"WLMPilot" wrote:

> Glad it worked out. Are you a pilot by chance? I am.
>
> Les
>
> "Michael M" wrote:
>
> > Hi
> > DUH !!!
> > I only followed half of your instructions, once I formatted A1 to text the
> > problem was solved.
> >
> > Thanks for your help and quick response
> >
> > Michael M
> >
> > "WLMPilot" wrote:
> >
> > > Michael,
> > >
> > > I used CHAR() to obtain the format that you want. However, the way I
> > > figured it out required you to input the number 0713020 (in this example) in
> > > a cell and then have another cell pick up that number and format it like you
> > > want it. If you copy and paste the formula below you will see what I mean.
> > > In my example, have A1 be the cell you enter the number 0173020. Format A1
> > > as TEXT. Then place the below formula in the cell you need to have it in
> > > Deg/Min/Sec.
> > >
> > > =IF(A1="","",LEFT(A1,3)&CHAR(186)&":"&MID(A1,4,2)&CHAR(146)&":"&RIGHT(A1,2)&CHAR(148))
> > >
> > > Hope this works for you.
> > > Les
> > >
> > > "Michael M" wrote:
> > >
> > > > Hi All
> > > > I am trying to input Degrees/minutes/seconds in the one cell.
> > > > I have setup a custom format thus:
> > > > [h]°:mm':ss''
> > > > However, when I type in 017°30'20'' I get 4152480°:00':00''
> > > > What am I doing wrong.
> > > > All I really want to do is type in 0173020 as a number and have excel make
> > > > it look like 017°:30':20''
> > > >
> > > > Any help would be appeciated
> > > > Regards
> > > > Michael M
> > > >