Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Importing text from a website
Hello.
Nursemaiding required for a perpetual lamer please. I want to put this stuff on an XL sheet or in another producer's spreadsheet if it will be easier. I wish to move the date over to the other side of the month and put the hour column in its own cells separate from the minutes as I wish to divide the hour (by 6) without touching the minutes. It must be a simple process I suppose but I wouldn't know where to start. Thank you in advance etc., etc.. http://aa.usno.navy.mil/cgi-bin/aa_m...s.pl?year=2006 2006 Phases of the Moon Universal Time 00000 New Moon 000 First Quarter 000 Full Moon 000 Last Quarter 00000000 d h m 0000000 d h m 0000000 d h m 000000 d h m 0000000000000000 Jan 6 18 56 Jan 14 9 48 Jan 22 15 14 Jan 29 14 15 Feb 5 6 29 Feb 13 4 44 Feb 21 7 17 Feb 28 0 31 Mar 6 20 16 Mar 14 23 35 Mar 22 19 10 Mar 29 10 15 Apr 5 12 01 Apr 13 16 40 Apr 21 3 28 Apr 27 19 44 May 5 5 13 May 13 6 51 May 20 9 21 May 27 5 26 Jun 3 23 06 Jun 11 18 03 Jun 18 14 08 Jun 25 16 05 Jul 3 16 37 Jul 11 3 02 Jul 17 19 13 Jul 25 4 31 Aug 2 8 46 Aug 9 10 54 Aug 16 1 51 Aug 23 19 10 Aug 31 22 56 Sep 7 18 42 Sep 14 11 15 Sep 22 11 45 Sep 30 11 04 Oct 7 3 13 Oct 14 0 26 Oct 22 5 14 Oct 29 21 25 Nov 5 12 58 Nov 12 17 45 Nov 20 22 18 Nov 28 6 29 Dec 5 0 25 Dec 12 14 32 Dec 20 14 01 Dec 27 14 48 The zeros in the legends are for spacing over usenet. I'm not bothered about the legends but it would be nice to keep the phases in sequence. (It's no big deal though.) |
#2
|
|||
|
|||
Weatherlawyer wrote:
Hello. Nursemaiding required for a perpetual lamer please. I want to put this stuff on an XL sheet or in another producer's spreadsheet if it will be easier. I wish to move the date over to the other side of the month and put the hour column in its own cells separate from the minutes as I wish to divide the hour (by 6) without touching the minutes. It must be a simple process I suppose but I wouldn't know where to start. Thank you in advance etc., etc.. http://aa.usno.navy.mil/cgi-bin/aa_m...s.pl?year=2006 2006 Phases of the Moon Universal Time 00000 New Moon 000 First Quarter 000 Full Moon 000 Last Quarter 00000000 d h m 0000000 d h m 0000000 d h m 000000 d h m 0000000000000000 Jan 6 18 56 Jan 14 9 48 Jan 22 15 14 Jan 29 14 15 Feb 5 6 29 Feb 13 4 44 Feb 21 7 17 Feb 28 0 31 Mar 6 20 16 Mar 14 23 35 Mar 22 19 10 Mar 29 10 15 Apr 5 12 01 Apr 13 16 40 Apr 21 3 28 Apr 27 19 44 May 5 5 13 May 13 6 51 May 20 9 21 May 27 5 26 Jun 3 23 06 Jun 11 18 03 Jun 18 14 08 Jun 25 16 05 Jul 3 16 37 Jul 11 3 02 Jul 17 19 13 Jul 25 4 31 Aug 2 8 46 Aug 9 10 54 Aug 16 1 51 Aug 23 19 10 Aug 31 22 56 Sep 7 18 42 Sep 14 11 15 Sep 22 11 45 Sep 30 11 04 Oct 7 3 13 Oct 14 0 26 Oct 22 5 14 Oct 29 21 25 Nov 5 12 58 Nov 12 17 45 Nov 20 22 18 Nov 28 6 29 Dec 5 0 25 Dec 12 14 32 Dec 20 14 01 Dec 27 14 48 The zeros in the legends are for spacing over usenet. I'm not bothered about the legends but it would be nice to keep the phases in sequence. (It's no big deal though.) There may be an easier way but this is what I would do. Go to the site and just and copy the data and paste into Excel. Then go to data text to columns and select delimited and the space as a delimiter. It will layout your numbers correctly but you will have to play with the headers and move the columns as necessary. gls858 |
#3
|
|||
|
|||
I'll have to book myself in for an evening class with this when the
school year starts. I got this far by subtituting the spaces for tabs with Word then copying and pasting MAY 10 20 00 20:00 MAY 18 07 34 07:00 MAY 26 11 55 11:00 JUNE 2 12 14 12:00 JUNE 9 03 29 03:00 JUNE 16 22 27 22:00 JUNE 25 01 00 01:00 JULY 1 19 20 19:00 JULY 8 12 53 12:00 JULY 16 13 55 13:00 JULY 24 11 02 11:00 JULY 31 02 25 02:00 AUG. 7 01 02 01:00 AUG. 15 05 13 05:00 AUG. 22 18 51 18:00 AUG. 29 10 19 10:00 SEPT. 5 16 27 16:00 SEPT. 13 19 37 19:00 SEPT. 21 01 28 01:00 SEPT. 27 19 53 19:00 OCT. 5 10 59 10:00 OCT. 13 08 53 08:00 OCT. 20 07 59 07:00 OCT. 27 07 58 07:00 NOV. 4 07 27 07:00 NOV. 11 21 15 21:00 NOV. 18 15 24 15:00 NOV. 25 23 11 23:00 DEC. 4 03 55 03:00 DEC. 11 09 03 09:00 DEC. 18 00 41 00:00 DEC. 25 17 22 17:00 Now I need to divide the end column by 6 and use an IF to show for example: 23:00 / 6 = 5 because (6 X 3 + 5 = 23.) Can you understand that? It is the "remainder" I want to show. |
#4
|
|||
|
|||
I'll have to book myself in for an evening class with this when the
school year starts. I got this far by subtituting the spaces for tabs with Word then copying and pasting MAY 10 20 00 20:00 MAY 18 07 34 07:00 MAY 26 11 55 11:00 JUNE 2 12 14 12:00 JUNE 9 03 29 03:00 JUNE 16 22 27 22:00 JUNE 25 01 00 01:00 JULY 1 19 20 19:00 JULY 8 12 53 12:00 JULY 16 13 55 13:00 JULY 24 11 02 11:00 JULY 31 02 25 02:00 AUG. 7 01 02 01:00 AUG. 15 05 13 05:00 AUG. 22 18 51 18:00 AUG. 29 10 19 10:00 SEPT. 5 16 27 16:00 SEPT. 13 19 37 19:00 SEPT. 21 01 28 01:00 SEPT. 27 19 53 19:00 OCT. 5 10 59 10:00 OCT. 13 08 53 08:00 OCT. 20 07 59 07:00 OCT. 27 07 58 07:00 NOV. 4 07 27 07:00 NOV. 11 21 15 21:00 NOV. 18 15 24 15:00 NOV. 25 23 11 23:00 DEC. 4 03 55 03:00 DEC. 11 09 03 09:00 DEC. 18 00 41 00:00 DEC. 25 17 22 17:00 Now I need to divide the end column by 6 and use an IF to show for example: 23:00 / 6 = 5 because (6 X 3 + 5 = 23.) Can you understand that? It is the "remainder" I want to show. |
#5
|
|||
|
|||
"Weatherlawyer" wrote
DEC. 11 09 03 23:00 DEC. 18 00 41 23:00 DEC. 25 17 22 17:00 Now I need to divide the end column by 6 and use an IF to show for example: 23:00 / 6 = 5 because (6 X 3 + 5 = 23.) Can you understand that? It is the "remainder" I want to show. Assuming the last col is col E, with the TIME contents such as: 23:00, 17:00, etc, and data is from row1 down, you could try: In say, F1: =MOD(SUBSTITUTE(E1*24,":",""),6) Copy F1 down Col F will return the "remainder" that you want from col E -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Max wrote: "Weatherlawyer" wrote DEC. 11 09 03 23:00 Assuming the last col is col E, with the TIME contents such as: 23:00, 17:00, etc, and data is from row1 down, you could try: In say, F1: =MOD(SUBSTITUTE(E1*24,":",""),6) Copy F1 down Col F will return the "remainder" that you want from col E Thanks Max, I'll give it a go. |
#7
|
|||
|
|||
You're welcome !
Thanks for posting back .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Weatherlawyer" wrote .... Thanks Max, I'll give it a go. |
#8
|
|||
|
|||
Max wrote: You're welcome ! Thanks for posting back .. I have to keep you sweet, I'm going to need a lot more from you :~) |
#9
|
|||
|
|||
Sample file was sent to OP (requested via private email)
Link to download sample file: http://flypicture.com?display=updone&id=qt30m6Q= (Copy paste entire line above into browser address, the link may not work if you click direct on it) File Name weatherlawyer_newusers_1.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#10
|
|||
|
|||
Max wrote: Sample file was sent to OP (requested via private email) Link to download sample file: http://flypicture.com?display=updone&id=qt30m6Q= (Copy paste entire line above into browser address, the link may not work if you click direct on it) File Name weatherlawyer_newusers_1.xls (Don't do this in AOL folks.) Thanks Max. I believe where I was going wrong was with which cell I was highlighting to do the dirty. I am once again very grateful indeed. I am definitely going to go on some sort of course with this stuff. TA Mike. |
#11
|
|||
|
|||
"Weatherlawyer" wrote:
Thanks Max. I believe where I was going wrong was with which cell I was highlighting to do the dirty. I am once again very grateful indeed. You're welcome ! I am definitely going to go on some sort of course with this stuff. You're actually already onto one of the best "course" right here in these excel newsgroups (some groups listed below). There's a lot that one can pick up by reading/trying out the posts and the responses given. microsoft.public.excel.newusers microsoft.public.excel.misc microsoft.public.excel.worksheet.functions microsoft.public.excel microsoft.public.excel.programming etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting data from a website changes text to date | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Text to Columns / Importing Data | Excel Discussion (Misc queries) | |||
importing multiple text files URGENT!!! HELP | Excel Worksheet Functions | |||
importing multiple text files??? | Excel Discussion (Misc queries) |