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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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.

"Matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

<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 are:

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 are:

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

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

<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 are:

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 are:

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.

In article <(E-Mail Removed)>,
"OG" <(E-Mail Removed)> wrote:

> "Matt" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
> 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.

I've used a similar approach as well, when I didn't want to bother with
string manipulations. Where I have both decimal and sexagesimal values
in a CVS file, before importing into Calc (which is very similar to
Excel -- this Mac is a largely Microsoft-free computer) I insert two
tabs after the former and separate the D (or H), M, & S of the latter
with tabs. That way the same formulae can be used for both types of
data, as the blank M & S fields will be treated as zeroes.

--
Odysseus

> 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

Are you sure on this ?

1 deg = 60 arcminutes
1/15 deg = 1 arcminute

surely...
1 deg = 60 arcminutes
1/60 deg = 1 arcminute ?

dylan wrote:
<snip>
> 1 deg = 60 arcminutes
> 1/15 deg = 1 arcminute
>
> surely...
> 1 deg = 60 arcminutes
> 1/60 deg = 1 arcminute ?

Yeah, that's for right ascension, not declination. A minute in right
ascension is not the same size as a minute in declination. Confusing,
ain't it?

> 1/60 deg = 1 arcminute ?
Is true for declination only.

- Canopus56