Sunday evening challenge to all

re-ordering a list of files

[esllou]

I have a long list of hrefs, like these four.

<A HREF="dvd/education/0790729717.html">The Color Purple</a>
<A HREF="dvd/education/0790732211.html">Rosewood</a>
<A HREF="dvd/education/1558908390.html">Dead Presidents</a>
<A HREF="dvd/education/B00004YS99.html">Uninvited Guest</a>

I want to order them alphabetically by title. I have tried in notetab and Word. Word reorders them fine, but according to isbn number before the .html bit.

Is there any way I can force a program to re-order by title....and what would that program be?

Many thanks

[TheDave]

excel spreadsheet

fill column A with your full line of HTML, ie:

<A HREF="dvd/education/0790729717.html">The Color Purple</a>

fill column B with:

=RIGHT(A1, LEN(A1) - FIND(CHAR(34), A1))

fill column C with:

=LEFT(B1, FIND(CHAR(34), B1)-1)

fill column D with:

=RIGHT(A1, LEN(A1) - FIND(">", A1))

fill column E:

=LEFT(D1, FIND("<", D1)-1)

fill column F:

="<a href=" & CHAR(34) & C1 & CHAR(34) & ">" & E1 & "</a>"

Now sort by column E making sure you expand the selection

Good Luck :)

[esllou]

I have a txt file with 1000 of these hrefs....would I have to enter all of them individually?

[TheDave]

no way! :P copy and paste! :P just select everything in the text file, copy it to the clipboard, go to column A of your spreadsheet and paste :)

[esllou]

I have NEVER used excel though it is on my pc....what you wrote was every so slightly double arabic to me. I am not even capable of copying and pasting stuff into excel. Real shame.

[TheDave]

Awwe come on! You ask for a solution, I give it to you, and you can't even copy and paste! I give up then. Good luck :)

[esllou]

hey, I don't give up that easily!

managed to do it with help of a mate here.

thanks a lot....now I have the real work to do.

[Mardi_Gras]

You will need to use Excel to do this, but you should be able to work it out.

1) Paste all your data into Excel. Everything will be in column A.

2)Highlight the entire column. Go to "data" and select "text to columns."

3) In the box that pops up, select "delimited," then "next," then in the box marked "other" type a "/" (no quotes).

4)Select finish. Excel will break your data into several columns. One Column will have this info: B00004YS99.html">Uninvited Guest< If I'm correct, this is what you want to sort by.

5) Select your entire spreadsheet. Go to "data", "sort", then select that column (the one you want to sort by). Be sure everything is selected. You can really screw up your data if you just sort one or two columns.

6) Your data should now be sorted the way you want. Now insert a blank column in front of your data.

7) in the first cell of the new column, insert =CONCATENATE(B1&C1&D1&E1) If you split your example data into more columns than these, add the additional columns.

8) Hit "enter." All of your data in the first row should now appear joined in the first cell.

9) Select the bottom right of the first cell and drag it down through your spreadsheet. The formula you created should move down for each row.

10) Once this is done, you can now delete all columns other than "A."

11) Copy your data out of column A and paste it wherever you need it.

added - in step 3 - don't use a / - use a >
that should break your data into two columns, one of which is the title.

in step 7, insert =CONCATENATE(B1,">"&C1,">")

[edited by: Mardi_Gras at 3:19 am (utc) on Sep. 15, 2003]

[Mardi_Gras]

Rereading your post - I think you should probably change the / in step 3 to a > I believe that will separate out just the title. Adjust the other steps to reflect.

[lawman]

esllou, this isn't a school homework project is it?

lawman

[Mardi_Gras]

Assuming you break this up by the > symbol, you will need to add it back in when you pull things back together. So your concantenate statement would be:

=CONCATENATE(B1,">"&C1)

Should work perfectly...

added - final notes are edited into post 8 -use that for guidance.

[edited by: Mardi_Gras at 2:49 am (utc) on Sep. 15, 2003]

[esllou]

lawman,

err, no. I am a teacher actually. No, I really needed it for a site project...to re-order the output of a script.

I worked with some others on the amazon forum to get another solution too, as well as those here. To use Word and change text to table using > as a delimiter, sort second column, then table to text it back. Simple.

well, it wasn't simple for me!

[Mardi_Gras]

well, it wasn't simple for me!

Well, I've typed out every single step of the process. I can't imagine making it any easier than that.

[esllou]

no, I meant it wasn't simple for me when I was confronted with the problem, but between this forum and the amazon one, I have been given a total of four solutions. someone even wrote a php script which did it at the click of a button!

mardi_gras: the excel thing worked as I just tried it too.

Thanks everyone.

[Mardi_Gras]

Sorry I don't have the programming skills to write a script for it ;)