Decimalising RA and Dec Values

Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Kind Regards,

Matt

Matt wrote:

Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Kind Regards,

Matt
Use search and replace, if you have to, copy the column out to a text editor
like TextPad and do the proper search and replace there, then copy it and
paste it back into the spreadsheet. Or... create a formula to read the cell
as text and convert it to decimal degrees and use the formula as part of
other formulas you plan on using to retrieve this data, then, you can leave
the cells as hours/mins/secs etc if you like the view that way.
Eric

"Matt" wrote in message
oups.com...

Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

================

Personally I would insert 6 blank columns after the RA & Dec column, then
use Text to Columns to break the data into 4 of them as
RAHours, RAMinutes, DecDegrees and DecMinutes

Then to get the decimal values
RAHours + RAMinutes/60
DecDegrees + DecMinutes/60

Perform calculations using the last two columns.

You can do this using the "right" and "left" functions in excel.
The basic concept is
=left( cell-reference, number of characters) or
=right( cell-ref, number of characters)

A couple of lines of cell formulae will have it sorted. Have a go
yourself or post back and I'll send you a file with ideas.

Al



"Matt" wrote in message
oups.com...
Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following
format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Kind Regards,

Matt

"Matt" wrote in message
oups.com...

snip Now so far I've been inputting the RA and Dec's in the
following format into an Excel spreadsheet: 00 03 34.43
[Matt needs decimal formatted degrees]

Matt,

I have a series of VBA modules for Excel - two of which will translate
between text RA and Dec and decimal degrees. Module 1 contains two
routines; sample invocations a

Module1.bas: Basic astrometry utilities.
http://members.csolutions.net/fisher...as/Module1.bas

? Convert_RA2DecDeg("03 00 00","HH MM SS","double") yields 45
? Convert_DMS2DecDeg("+34 43 00","sDD MM SS","double") yields 34.716667

You'll need to use text functions (IF, MID, Left, Right, Concanetate) to
coax you input into a form recognized by the functions.

Convert_RA2DecDeg recognizes the forms:

"HHMMSS"
"HH MM SS"
"HH MM SS.SS"
"HHMMSS.SS"
"HH:MM:SS"
"HHh MMm SSs"

Convert_DMS2DecDeg recognizes the forms:

"sDDMMSS" ' Sign is optional
"sDD MM SS"
"sDD MMm SS.S"
"sDDd MMm SSs" ' This format includes sDD° MM' SS"
"sDDDMMSS"
"sDDD MM SS"
"sDDD MM SS.S"
"sDDDd MMm SSs"

A third function, Convert_ReformatTxtCoor(), reformats coordinate text
strings into a various text formats.

My amateur Excel observing spreadsheet project utilizes these functions:

Deep Sky Observing Planning Spreadsheet (July 2006)
http://members.csolutions.net/fisher...htProject.html

The functions are all after Meuss or Duffett-Smith's books.

Clear skies.

- Canopus56

P.S. -

Other modules in the series a

Module2.bas: Stellar magnitude utilities.
http://members.csolutions.net/fisher...as/Module2.bas

Module3.bas: Astrophotography utilities.
http://members.csolutions.net/fisher...as/Module3.bas

Module4.bas: Solar and lunar position.
http://members.csolutions.net/fisher...as/Module4.bas

"Matt" wrote in message
oups.com...
Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following
format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Have you considered breaking the values out into
their own columns? (You can resize the columns
to preserve the look for printout).

One way to do this is to select the column of
cells that contain the "compressed" formatted
text, then on the tool bar select

Data -- text to columns

and follow the "wizard".

Firstly may I thank everyone for such great responses.

I've given the method using the Excel modules a go, but I think it is a
little beyond my ability with Excel at present. Basically I'm not sure
how to input the data into the module, so I just get the "#VALUE"
coming into the cell whenever I try to use it, even when I try copying
and pasting in your example as follows:

? Convert_RA2DecDeg("03 00 00","HH MM SS","double") yields 45

though it does complain about the question mark, so I'm sure I'm doing
something wrong. If you could give me a bit of an idiots guide
regarding how I input a piece of data into this module that would be
great.

The method of using the LEFT and RIGHT functions in Excel was also very
intriguing and works great for getting the degrees and arc seconds out
of a coordinate. However, I'm unable to get the arc minutes by itself
because it is in the middle (i.e. I either use LEFT (J2, 6) and get the
first 6 characters which includes the degrees bit as well, or RIGHT
(J2,6) and get the arc seconds bit). Is their something really obvious
I'm missing with using this function? Could their be a MIDDLE
function or something like that?

Kind Regards,

Matt

Have you considered breaking the values out into
their own columns? (You can resize the columns
to preserve the look for printout).

One way to do this is to select the column of
cells that contain the "compressed" formatted
text, then on the tool bar select

Data -- text to columns

and follow the "wizard".

I had no idea this function existed - I'll give it a try as well...

Kind Regards,

Matt

snip
The method of using the LEFT and RIGHT functions in Excel was also
very
intriguing and works great for getting the degrees and arc seconds
out
of a coordinate. However, I'm unable to get the arc minutes by
itself
because it is in the middle (i.e. I either use LEFT (J2, 6) and get
the
first 6 characters which includes the degrees bit as well, or RIGHT
(J2,6) and get the arc seconds bit). Is their something really
obvious
I'm missing with using this function? Could their be a MIDDLE
function or something like that?

Kind Regards,

Matt


Assuming J6 is one of a column of entries in a format like "00 03
34.43"
then cell you can have some cell (let's say K6) with entry
=LEFT(J6, 6) which gives you the six leftmost characters of J6. Why
six? If the degrees value is 99 then you need some way to ensure you
get all of the relevant characters. (It's worth noting that in cell J6
the "numbers" are not really numbers as far as Excel is concerned -
they are treated as text. Excel has ways around this). So now cell K6
either has (the degrees then a space then the minutes then a space) or
if the degrees value was greater than 99 then K6 has (degrees then a
space then the minutes). So far so good?

Then we use cell L6 to extract the degrees with
=VALUE(LEFT(K6,3))
working from the inside brackets out this formula takes the left 3
"numbers" then converts the text value to an integer (which is the
degrees value that you wanted). The neat bit is that Excel will
automatically filter out a trailing or leading space. Finally use cell
M6 to extract the minutes with
=VALUE(RIGHT(K6,3)

The overall concept is that to get to the middle you split the thing
in two; the middle is now one of the ends of the two new pieces...
:-)

Matt wrote:
snip all

In a cell you would use:

=Convert_RA2DecDeg("03 00 00","HH MM SS","double")

I suggest you use a hybrid of "OG" and Greg's suggestions. When
dividing to make hours and minutes uniform to decimal degrees, remember
that a arcminute and arcsecond of right ascension and an arcminute and
arcsecond of declination do not have the same value.

For right ascension -

360 deg = 24 hours
15 deg = 1 hour
15 deg = 60 arcminutes
1 deg = 4 arcminutes
1/4 deg = 1 arcminute
1/4 deg = 60 arcseconds
1/4 * 1/60 deg = 1 arcsecond
0.004167 deg = 1 arcsecond

There are 15 degrees in an hour of right ascension (360 degrees / 24
hours ra).

There are 0.25 degrees in an arcminute of right ascension ( ( 15
degrees / 1 hour ra) / ( 60 arcmins / 1 hour ra) = 0.25 degrees)

There are 0.004167 degrees in an arcsecond of right ascension ( ( 15
degrees / 1 hour ra) / ( 3600 arcseconds / 1 hour ra) = 0.004167
degrees)

So to convert from ra to decimal degrees, you might use something like:


= (iHours * 15) + (iMin * 0.25) + (dSec * 0.004167)

or

= (iHours * 15) + (iMin * 0.25) + ( ( dSec * 15) / 3600 )

In declination, the values a

360 deg = 360 degrees
1 deg = 1 deg
1 deg = 60 arcminutes
1/15 deg = 1 arcminute
1 arcminute = 60 arcseconds
1/15 deg = 60 arcseconds
1 ( 15 * 60 ) = 1 arcsecond
0.000278 deg = 1 arcsecond

Verses in declination, there are 0.01667 degrees in an arcminute of
declination ( 1 deg / 60 arcminutes = 0.01667 degrees).

Verses in declination, there are 0.000278 degrees in an arcsecond of
declination ( 1 deg / 3600 arcseconds = 0.000278 degrees).

To convert declination to decimal degrees you might use something like:


= Round(((iDeg + (iMin / 60) + (dSec / 3600)) * iSign), 6)

The Excel function to extract the middle of a string is "MID"; to
convert the string to a value is (Value), e.g. -

=MID("00 03 00",4,2) yields "03"
=Value(MID("00 03 00",4,2)) yields 3.0

Hope that helps.

- Canopus56

P.S. -

The main books on astronomical algorithms a

Duffet-Smith1988: Duffet-Smith, P. 1988 (3ed). Practical Astronomy with
Your Calculator. Cambridge Press.
http://www.willbell.com/
http://adsabs.harvard.edu/cgi-bin/np...1988QB62.5.D83.....

Meeus, J. 1998. 2ed. Astonomical Algorithms. Willmann-Bell. ISBN
0-943396-61-
http://www.willbell.com/

Montenbruck, Oliver. 1989. Practical ephemeris calculations.
Springer-Verlag.

Duffet-Smith can usually be found in local libraries; Meeus and
Montenbruck at university libraries.