OT: Excel question

Dec 21, 2006 6 Replies

Rate this thread:

Grant Erwin 19 years ago

Hi. I want to convert a number which has units of degrees e.g. 26.87° to HMS format, in other words degrees minutes and seconds. Actually, I want it rounded to the nearest 10 seconds, but that's just a detail. I know how to make up the number in two different spreadsheet fields, but what I want to know is how to make an expression in Excel, part of which is a calculated number and part of which is units. A good example would be a number like $56.21 - now THAT would be useful. Actually, in this case 26.87° -> 26°50' (26 degrees 50 minutes) rounded to the nearest 10 minutes. So this is even harder - an expression containing TWO calculated numbers.

Do you know how to do this?

Thanks, Grant

Vote

cascadiadesign 19 years ago

Grant,

My numbers may make no sense - this is just to show the concept:

cell C21 =3D 2*3.3 cell C22 =3D 3*7.1 cell C23 =3D TEXT(C21,"###.#")&"deg "&TEXT(C22,"###.#")&"min"

Displayed result:

6=2E6deg 21.3min

substitute any text you want inside the " "

Grant Erw> Hi. I want to convert a number which has units of degrees e.g. 26.87=B0 t= o HMS

Vote

Grant Erwin 19 years ago

Thanks! I get it completely now .. duh ..

GWE

snipped-for-privacy@comcast.net wrote:

Vote

joelblatt 19 years ago

Vote

clutch 19 years ago

The other thing you can do is create a excel vba function which is like a new command in excel. It is a lot cleaner than using up a bunch of cells to do the coversion.

Please pardon this crappy example, I haven't done a VBA function in 6 years and I'm gettting a MSOHELP.exe generated an error message at the moment when I try to use the help file.

Using this as a start to grasp the idea:

formatting link

Try cutting and pasting this:

Function DMS(DMS1 As String) d = Int(DMS1) frac = DMS1 - d m = Int(frac * 60) frac = (frac * 60) - m s = frac * 60 DMS = Str(d) + "D" + Str(m) + "M" + Str(s) + "S" End Function

There are a bunch of tips I found googling using search terms 'create vba excel function'.

hth,

Wes S

Vote

Lloyd E. Sponenburgh 19 years ago

VBA functions are one way, but this can be done simply with ordinary Excel functions, too.

In the function below "A1" is the cell containing a NUMBER that represents degrees and fractions. If you want to do this on a string, a surrounding couple of functions to reduce it to a number will be necessary.

This displays the number in A1 as degrees, minutes, and seconds, as "###d##s##s".

=CONCATENATE(TEXT(TRUNC(A1,0),0),"d",TEXT(TRUNC((A1-TRUNC(A1,0))*60,0),0),"m",TEXT(((A1-TRUNC(A1,0))*60-TRUNC((A1-TRUNC(A1,0))*60,0))*60,0),"s")

LLoyd

Vote

Ian Malcolm 19 years ago

You may find the following format strings useful. I use them for GPS positions stored as numbers of the form {+/-}DDD.MMmmm where DDD is integer degrees and MMmmm is minutes and decimal minutes with an implied decimal point MM.mmm For Latitude: [>=0]00°." "00\.000"' N";00°." "00\.000"' S" For Longitude: [>=0]00°." "000\.000"' E";00°." "000\.000"' W"

To convert numbers in degrees and decimal degrees to a compatible form try the following formula (original number in A1): +A1*0.6+TRUNC(A1)*0.4

N.B I run Excel 5 so you may need to tweak it a bit fir later versions.

It all works very cleanly and nicely as no intermediate cells are required for the convesion.

Vote