Gee! What happened?


Peo


"Dave Peterson" wrote in message
...
Just an unfortunate line break:

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),{"The";"A"},0)),
TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)-1),"")),A2)

(all in one cell)

Peo Sjoblom wrote:

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well

=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND ("
",A2)-1),"")),A2)

copy down as long as needed, select both columns and sort ascending on
the
help column

I am assuming that the first film title is in A2

--

Regards,

Peo Sjoblom

"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type
The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and
"A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and
"A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so,
how?

Thanks,
Amanda


--

Dave Peterson