Alphabetizing in Library Format

E

Ekroeten

Hi,

I'm relatively new to Excel so I was wondering if there is a way to
have the sort Data Protocols ignore "the", "an", and "A" like the
Library rules do?
 
V

vezerid

This can be accomplished with a formula.
Let us say you have the titles in column A:A
In an auxiliary column put the articles you want to ignore. Let us say
in
K1:K3 you have "The", "A", "An".
Now, in B2 you use the following formula:

=IF(ISNA(VLOOKUP(LEFT(A2,FIND("
",A2)-1),$K$1:$K$3,1,0)),A2,IF(ISERROR(FIND(" ",A2)),A2, MID(A2,FIND("
",A2)+1,LEN(A2))))

This formula will leave, in B2, the title stripped of the first word,
if it is one of those 3. You can then sort by column B:B.

HTH
Kostis Vezerides
 
Top