Need a formula or other way to sort excluding the and a and an

[buzfar]

Hello there;

This site has always been there for me on the InDesign side, and now I have found this. I am in need of a way to sort an excel document.

I have a listing of book titles, over 1200 books, and need to sort on the title, but omit "the" "a" and "an" if the title begins with these three words. Is there a formula or another way out there to enable me to do this?

Thank you so much for you help!

Allene

 

[AnotherHiggins]

You could just use a nested If function in an adjacent cell, fill the formula down for all records, then sort on that column.

=IF(LEFT(A2, 2) = "a ", MID(A2, 3, 999), IF(LEFT(A2,3) = "an ", MID(A2, 4, 999), IF(LEFT(A2, 4)="the ",MID(A2, 5, 999), A2)))



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Variation, on a theme...
[tt]
=IF(OR(LEFT(A2,2)="A ",LEFT(A2,3)="An ",LEFT(A2,4)="The "),RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2)
[/tt]
[sub]I never got thru all of Carnival of Venice.[/sub]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]