How to I sort titles alphabetically like in a library?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)))
 
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
 
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
 
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
 
Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.
 
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
 
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)
 
Thanks! That worked!

Dave Peterson said:
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)
Click to expand...
 
Gee! What happened?


Peo


Dave Peterson said:
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)
Click to expand...
 
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>.)
 
I wasn't trying to take credit for the formula (this time! <vbg>.)
Click to expand...


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

Peo
 
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!).
 
That was one of Dave's rare intentional misspellings. <g>

Maybe he thinks there are too many "ehs" in Canadian.
 
The original mistake wasn't on purpose. The subsequent have been on purpose
(mostley).

<vbg>
 
Back
Top