quote:

---

Here's a non-commercial solution.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

To set up a helper column, where "the", "a", or any other First Word is
eliminated, you can use the formula:

=REGEX.SUBSTITUTE(A1,"^(A|The)\s",,,,FALSE,TRUE)

The Regular Expression "^(A|The)\s" is looking to match the pipe-separated
list of phrases that are within the parentheses, and replace it with nothing.

So you could, for example, expand it as desired:

"^(A|The|An)\s"

If you wanted to have a range that would dynamically sort, you could have a
data entry column named "rng" where you just enter the film names sequentially.
"rng" cannot refer to an entire column.

In your display column, enter the formula:

=INDEX(VSORT(rng,REGEX.SUBSTITUTE(rng,"^(A|The)\s" ,,,,
FALSE,TRUE),1),ROWS($1:1)+COUNTBLANK(rng))

and copy/drag down. This column will display the entries in "rng" in sorted
order.

This formula will give a #REF! error if you have more formula rows than
entries. This latter can be taken care of either in the formula itself, or by
using conditional formatting to check for the #REF! error and change the font
to the background color if it is present.

The dynamically sorted solution can take a long time depending on how large
"rng" is.
--ron

---

quote:

---

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

int main(void)
{
char temp[1024];
int counter;
FILE *fp,*output;
if ((fp=fopen("abbott.csv","r"))==NULL)
{
printf("Can't open input file, aborting");
exit(1);
}
if ((output=fopen("costello.csv","w"))==NULL)
{
printf("Can't open output file, aborting");
exit(1);
}

while (fgets(temp,1024,fp) != NULL)
{
if (!strncmp(temp,"The ",4))
{
counter=4;
while (temp[counter] != ',')
{
temp[counter-4]=temp[counter];
counter++;
}
temp[counter-4]=' ';
temp[counter-3]='T';
temp[counter-2]='h';
temp[counter-1]='e';
}
else if (!strncmp(temp,"A ",2))
{
counter=2;
while (temp[counter] != ',')
{
temp[counter-2]=temp[counter];
counter++;
}
temp[counter-2]=' ';
temp[counter-1]='A';
}
fprintf(output,"%s",temp);
}

fclose(fp);
fclose(output);
return(0);
}

---

The program looks for a file named abbott.csv and creates a new file named costello.csv. We assume that the maximum length of any single line of your spreadsheet is not more than 1023 characters; if it is greater than that, then change the number in the "char temp" declaration and the fgets to match or exceed the maximum number of characters in a single line, plus one.

We assume that the movie title field is the first column of your spreadsheet.

After running the program, import costello.csv back into your spreadsheet program. Now you can sort it to suit; all of the A the The are at the end of the movie titles and the rest of the columns are unchanged.

For the sake of simplicity I didn't put any punctuation before the A or The other than a single space. You can always do a search and replace on that column after you have it back in the spreadsheet to make it look like whatever you choose. Again, we're going to let the spreadsheet do the heavy lifting.

There is no particular limit to how many lines you can process with this program -- it just reads one line at a time and keeps going until it hits the end of the input file.

This program could obviously be beautified and bulletproofed quite a bit, but for a one-off job like what you're doing here, this will work. I just tried it here and there were no explosions, fires or earthquakes, so I guess it works. Note that this program comes with my standard two-part guarantee: If you break it, you get to keep both pieces.
 

---

Posted by Mark J. Marshall (Member # 1409) on 08-31-2011, 04:38 AM:
 
Assuming the movie titles are in column A, starting in row 2, put this in the second row of the column you want to make the new title and drag it down. (If it's in a different cell, just change the four "A2"s to whatever the first cell of movie titles is.)

=IF(LEFT(A2,4)="The ", CONCATENATE(RIGHT(A2, (LEN(A2)-4)), ", The"), A2)

To put that newly formatted column back in column A just highlight it and copy it, then "Paste Special" on column A and select "Values".
 

---

Posted by Frank Angel (Member # 248) on 08-31-2011, 05:30 AM:
 
Hmmm, that seems like a lot of work for simply leaving off the unwanted article and starting with the full word. What's wrong with "Movie Title, The"? That's the way we do it.
 

---

Posted by Chris Slycord (Member # 4239) on 08-31-2011, 09:52 AM:
 
Frank, he's trying to use "Movie Title, The".

He's got a bunch of entries like "The Movie Title" and wants to quickly automate it to change them to "Movie Title, The" so that he doesn't have to change them by hand.
 

---

Posted by Frank Cox (Member # 6258) on 08-31-2011, 11:21 AM:
 
Indeed, Chris, and that is exactly what that little program does.

Assuming input lines that look like this:

The Big Man, him, her
A Large Pizza, me, you

The program will change those lines to look like this:

Big Man The, him, her
Large Pizza A, me, you

After importing the csv file back into the spreadsheet you can add a comma before the The by doing a search-and-replace on " The" and replacing it with ", The" and the same with the A. I could have put that functionality into the little program, but why bother when he's just putting it back into a spreadsheet that can do that part for him anyway.
 

---

Posted by Mark J. Marshall (Member # 1409) on 08-31-2011, 11:55 AM:
 
I missed that you wanted to move "The" AND "A". This line will do both, and leave everything else alone.

=IF(LEFT(A2,4)="The ",CONCATENATE(RIGHT(A2,(LEN(A2)-4)),", The"),(IF(LEFT(A2,2)="A ",CONCATENATE(RIGHT(A2,(LEN(A2)-2)),", A"),A2)))
 

---

Posted by Frank Angel (Member # 248) on 08-31-2011, 03:29 PM:
 
Damn Mark, you are good!

I am still muddlng along as a first-time user of Excel. For years I had been using a sweet little database program FileExpress which did EVERYTHING I ever wanted to keep trailers, 1sheets, prints nicely organized. No matter how you sorted things in FileExpress, every entry had a permanent RECORD number, so you could get everything back to the way it was entered. Imagine my surprise (and horror) when the first time I entered maybe 200 plus 1Sheet titles tried to arranged the NAME row in alphabetical order, that it didn't take all the other rows with it, essentially taking the information and shuffling it like a deck of cards....and screwing me.

Well, I could just put everything back in RECORD number, right? There were RECORD numbers in the very first column; I can sort on that. Not it Excel. No such use of record reference. And once columns are out of order and shuffled, they are OUT OF ORDER and never to be UNshuffled again.

Only then did I fully understand what that little dialogue box meant when it asked if I wanted to sort EVERY column or just the TITLE column that I was on. Silly me thought, "Well, no, I only want to alphabetize the one I have highlighted. Uh oh. This looks bad, What happened? Let me try to sort all of the columns." BAZOOM...ruination. Now nothing matched. Titles have lost their locations numbers and location numbers no longer matched their content number as well as all the other data. Better ask on Google about this. Google came up with plenty of answers, all of which in one form or another said stuff like "You're doomed, stupid."

It's back to FileExpress.

PS, In my defense, when software is going to make that kind of catastrophic change, USUALLY you expect it to be more alarmist, like "Pressing enter will PERMANENTLY loose the present relationship between the column you are sorting and the rest of the data. DO YOU WANT TO DO THIS?" MS is usually pretty good about its warnings. With Excel I think they were much too tame.
 

---

Posted by Steve Guttag (Member # 268) on 08-31-2011, 04:39 PM:
 
CTL-Z didn't bring you back to pre-disaster? You can multiple press it to to go back several changes worth.

However, the big lesson here is good housekeeping. Before I change a document, I always do a "Save As" with the new date in the file name...space is cheap, redoing work is expensive and time consuming.

I believe that Excel also creates a .bak file so there is a means of reverting to a previous version (Word does). I must admit, I've never had to use either the .bak or unsort something.

-Steve
 

---

Posted by Mike Blakesley (Member # 26) on 08-31-2011, 05:43 PM:
 
Thanks everyone! MARK! I tried that and it worked perfectly. Thank you.

I am still amazed that Excel doesn't have this "built-in" somewhere. You would think it would be something that's needed.
 

---

Posted by Mike Blakesley (Member # 26) on 08-31-2011, 11:32 PM:
 
My list of movies has migrated through several programs. I think I first created it in WordPerfect 5.1 back when that was "the" program to have -- I didn't have a spreadsheet program at the time and probably didn't know what a spreadsheet was, actually. WP could create comma-delimited files, so that was the key of course. The list has the title, rating, studio, attendance and gross. If I had it to do all over again I'd have made it with each day broken down separately, just for fun.

I remember first getting Excel and being amazed that it could actually do something with this ancient file and I wouldn't have to re-enter everything. The data has stayed with me since MS-DOS but I'm still reluctant to throw out the old paper records...there's just something cool about seeing all those binders taking up space in the storeroom.
 

---

Posted by Frank Angel (Member # 248) on 09-01-2011, 05:53 PM:
 
Ah, WordPerfect...the godfather of wordprocessors and a FANTASTIC program. EXTREMELY intuitive, hardly EVER had to open a manual, just hunted around a bit and you could make it do almost anything you wanted. Ah, manuals....books where you could ACTUALLY find answers questions you had and that would help you solve problems. The WP manual was huge -- a bible-sized book that could get you thru any question, and in the process, you could also find other wild things the program could do for you -- that's the great nature of a book, you can PAGE thru it and discover great stuff accidently.

But then along came effin Microsoft and decided to crush WP like a bug and gave us Word with no manual and as far as it being intuitive software, well, let me wipe this sarcastic snear off my face.
 

---

Posted by Steve Guttag (Member # 268) on 09-01-2011, 08:55 PM:
 
Really? You are going to put WP up against Word? I've been on Word since 3.0 (in the Mac world)...Word Perfect was anything BUT intuitive. I ran many circles around World Perfect. Word Perfect where people thought they had everything aligned up because on the screen it was monospace but in the printer it was proportional. How many keyboards had those stupid overlays with four colors on them (and even keycaps to match, in some cases) just so people could "figure out" what it took to print (Shift-F7). Over in the Mac world...where Word really started...it was cloverleaf-P and in P for Print... and cloverleaf-S for save...and so forth...how did you save in Word Perfect?

PC's first dealings with Word were definitely not up to Word Pefect...the first attempts at getting the Mac version ported over begot "WinWord"...then WinWord 2...still not the Mac interface but getting closer...With Word 6...they tried to get a common program...however, us Mac guys were quite happy with Word 5.1 and RETURNED Word 6 for being a sluggish pig in order to get the PC side to have the same program...Microsoft responded with an updated Word 6 (6.1, I think) that was MUCH faster and more of an upgrade to the Mac version of Word...from that point onward, Word was pretty much the same program...they still leapfrog each other on revisions but most of the Mac version is still there and it was always better than Word Perfect.

-Steve
 

---

Posted by Frank Angel (Member # 248) on 09-01-2011, 09:53 PM:
 
We'll have to disagree. All I know was that I did fine with WP and ever nearly had the frustrations that I had later when we went to WinWord. I started with the monospace DOS version of WP, but only for a short while -- then we quickly moved to WP's WYSIWYG DOS version where what you saw was what actually came out of the printer. We did posters, newsletters, brochures with ease, pictures, a great variety of mixed fonts, multiple columns for newsletters and great mail-merge work. Our current logo was created in WP and we still use it today.

Maybe the Mac version wasn't as sweet, but yes, I call it very intuitive. The key cheats that you seem to dislike were great, right there in front of your eyeballs and within no time at all you had them all 12 F keys X4 memorized. Then along came Word and all those commands were NOT in front of your eyeballs, they were BURRIED quite illogically in menus after menus. Oh sure, after a decade of working with Word, it's no problem, but all I can tell you is that I found WP much easier at the very start that I did Word. Maybe this is because our first computers were running CP/M, so coming to a much more sophistocated WP in DOS seemed like an intuitive miracle. Try dealing with wordprocessing in the CP/M OS and then switch to WP; it's all relative.

Actually I take that back; our first computers were a small network (only 8 workstations) -- an ALTOS system running on an early Linux. Just BREATH on that thing and all 8 stations would freeze. The server was so sensitive that if you accidently bumped into the table it was sitting on, the hard drive would crash...I mean, ruined. You gave that thing wide berth and walked quietly. When we had to move it, they walked with it as if they were carrying nitroglycerin.
 

---

Posted by Mark J. Marshall (Member # 1409) on 09-01-2011, 09:54 PM:
 
No problem, Mike! That was an easy one!

When I first saw Word Perfect it was the old solid blue screen DOS based version. 5.0? I never really quite got the hang of it.

I wish I could get fluent with vi. I find myself needing to use it on our servers every once in a while. Wish I knew more about how to do the basics with it.
 

---

Posted by Frank Cox (Member # 6258) on 09-02-2011, 12:12 AM:
 
There are numerous vi tutorials (just run "vi tutorial" through google) and the best book I'm aware of is Learning the Vi Editor, Sixth Edition
 

---

Posted by Mike Blakesley (Member # 26) on 09-02-2011, 12:51 AM:
 

quote: Mark J. Marshall

---

When I first saw Word Perfect it was the old solid blue screen DOS based version. 5.0?

---