I've got an Excel Spreadsheet, with observations in the following format:
34.07500
68.17400
102.25075
i.e. XX degrees decimal xx minutes xxx seconds/tenths of seconds.
(The third one, is a mean: 102 degrees, 25 minutes, 7.5 seconds.
I'm trying to write a "format" in Excel to show these as
34-07-50
68-17-40
102-25-07.5
so that I can cut and paste them as text directly into Starnet.
Any ides?
I don't have a slick way, but here's a messy one.
First column is the number you have, say cell A5
Second column is = TRUNC(A5)
Third column is = -TRUNC(100*(A5-B5))
Fourth column is = -ROUND(10000*(A5-B5+C5/100),1)
Fifth column is formatted as TEXT and =B5&C5&D5
Optional: decimal degrees = A5-B5/60-C5/3600
(Negatives because we put the minus sign in to get it in text)
[rant]That packed format was good for the old HP calculators with limited display capability, but it should have stayed there. I hate it anywhere else. Any device now should be able to display the degrees, minutes, and seconds separately. [/rant]
> I don't have a slick way, but here's a messy one.
>
While I'm typically an "elegant solution" kind of guy, I know when "Any Port in a Storm" applies.:-D
Thanks very much.
Formatting only, but not converting to decimal degrees, can be accomplished as follows:
Given cells
A1 -> 34.07500
A2 -> 68.17400
A3 -> 102.25075
In another three cells, enter the following formulae:
cell_1 -> =SUBSTITUTE(TEXT(A1,"0.-00-00.00"),".","",1)
cell_2 -> =SUBSTITUTE(TEXT(A2,"0.-00-00.00"),".","",1)
cell_3 -> =SUBSTITUTE(TEXT(A3,"0.-00-00.00"),".","",1)
which will result as:
cell_1 -> 34-07-50.00
cell_2 -> 68-17-40.00
cell_3 -> 102-25-07.50
just....
WOW.:-)