Sorting formula?

Hello, i have been hand out a very large list of book titles (in french)..many of titles have "La" or "Le" or "L'" in front the the first word...would there be a solution for me when i'm sorting that these could be bypass?...hope someone has a bright idea...it would save me lots of time

Create a column beside with a formula like this:
=IF(OR(LEFT(A1,2)="Le",LEFT(A1,2)="L'",LEFT(A1,2)="La"),MID(A1,4,LEN(A1)),A1)
It will trim the first 3 characters -you can change to 2 using
MID(A1,3,LEN(A1))- of the cell if it fills any of the criteria, or leave it
untouched if not.
Then sort by this column.
--
It is nice to be important, but it is more important to be nice


"crestars" wrote:

>
> Hello, i have been hand out a very large list of book titles (in
> french)..many of titles have "La" or "Le" or "L'" in front the the
> first word...would there be a solution for me when i'm sorting that
> these could be bypass?...hope someone has a bright idea...it would save
> me lots of time
>
>
> --
> crestars
> ------------------------------------------------------------------------
> crestars's Profile: http://www.excelforum.com/member.php...o&userid=32267
> View this thread: http://www.excelforum.com/showthread...hreadid=520181
>
>

Here's one way, assuming your title is in column A and the first title
is in cell A1: insert a blank "helper" column next the the title column
(so the helper is column B). Paste this formula into B1:
=IF(MID(A1,1,3)="La ",MID(A1,4,LEN(A1))&",
"&MID(A1,1,3),IF(MID(A1,1,3)="Le ",MID(A1,4,LEN(A1))&",
"&MID(A1,1,3),IF(MID(A1,1,4)="Les ",MID(A1,5,LEN(A1))&",
"&MID(A1,1,4),IF(MID(A1,1,3)="L' ",MID(A1,4,LEN(A1))&",
"&MID(A1,1,3),IF(MID(A1,1,2)="L'",MID(A1,3,LEN(A1))&",
"&MID(A1,1,2),A1)))))

This formula checks for La, Le, Les, L', and "L' " (note the space:
it's intended to catch keyboard entry errors) and moves those definite
articles to the end of the title entry. If the definite articles are
not present, the entire title as entered is returned. Then sort on the
helper column. Note that you must specify entire rows to sort, in this
context.

boy you guys are very very good and so nice..thanks a bunch!