Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alphabetizing in Library Format
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? -- Ekroeten |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alphabetizing in Library Format
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 Ekroeten wrote: 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? -- Ekroeten |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditonal Format with a date format | Excel Discussion (Misc queries) | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) |