| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Degrees
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Degrees
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Degrees
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Degrees
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Degrees
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Degrees
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column heading at 45 degrees | Excel Discussion (Misc queries) | |||
| degrees formula | Excel Worksheet Functions | |||
| convert decimal degrees to degrees minutes seconds | Excel Discussion (Misc queries) | |||
| excel formula to convert lat/lon degrees to DDD MM SS.SS? | Excel Worksheet Functions | |||
| converting from digital degrees to degrees minutes seconds | Excel Worksheet Functions | |||
Linear Mode
