posted 08-31-2011 12:17 AM                       

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

---

posted 08-31-2011 03:19 AM                       

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);
}

---