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

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!


--
crestars
------------------------------------------------------------------------
crestars's Profile: http://www.excelforum.com/member.php...o&userid=32267
View this thread: http://www.excelforum.com/showthread...hreadid=520181