Degrees

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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

 

[Guest]

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

Les

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

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

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

 

[Guest]

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

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

Les

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

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

:

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