1. Home
  2. ⏣ General Metalworking
  3. OT: Excel question

OT: Excel question

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

Reply to
Grant Erwin
Loading thread data ...

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 " "

Ed

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

Reply to
cascadiadesign

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

GWE

snipped-for-privacy@comcast.net wrote:

Reply to
Grant Erwin

Reply to
joelblatt

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

Reply to
clutch

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

Reply to
Lloyd E. Sponenburgh

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.

Reply to
Ian Malcolm

PolyTech Forum website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.