How to I sort titles alphabetically like in a library?

[Amandar097]

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

 

[Tevuna]

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]

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]

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]

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]

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

 

[Amandar097]

OK - thanks so much for your help and patience with all my questions

 

[Peo Sjoblom]

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

 

[Dave Peterson]

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)

 

[Amandar097]

Thanks! That worked!

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]

Gee! What happened?


Peo

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)

 

[Dave Peterson]

The space at the end of your first line was turned into a linefeed.

I wasn't trying to take credit for the formula (this time! <vbg>.)

 

[Peo Sjoblom]

I wasn't trying to take credit for the formula (this time! <vbg>.)

You got it nevertheless
Since you are a fellow Swede (at least somewhat) I won't be making any fuss
<vbg>

Peo

 

[Dave Peterson]

Man, oh, man.

First, I have the Candian Quality Council (CQC) on my case.

Now, I have to watch out for the SIS (Swedish Integrity Society), too.

====
Keep an eye out (ouch!). You may see that formula posted (without attribution
after a few times!).

 

[Peo Sjoblom]

First, I have the Candian Quality Council (CQC) on my case.

So you are a candy tester, big deal.


<g>

 

[Debra Dalgleish]

That was one of Dave's rare intentional misspellings. <g>

Maybe he thinks there are too many "ehs" in Canadian.

 

[Dave Peterson]

The original mistake wasn't on purpose. The subsequent have been on purpose
(mostley).

<vbg>