Splitting names by delineator not occurring in the same location

[row2000]

I've attached the workbook with my desired before and after. I'm looking to split the names but I already have your basic trim formula that will split cookie cutter names (IE: row 2-3). I'm working with Spanish names where "Cruz" ends up in the last name column when I need it to be "de la Cruz". I have a lot of "de" names and can make it work if I can make a "de" exception to my formula and make it split only on the last occurrence of the "de" since sometimes I have long names (see row 5).

I'm using:

=LEFT(A2,FIND(” “,A2,1)-1)
=RIGHT(A2,LEN(A2)-FIND(” “,A2,1))

[Bo_Ry]

Plaese try at
B2
=LEFT(A2,LEN(A2)-LEN(C2)-1)

C2
=TRIM(RIGHT(IF(ISERR(SEARCH(" de ",A2)),SUBSTITUTE(A2," ",REPT(" ",50)),SUBSTITUTE(PROPER(A2)," De ",REPT(" ",50)&"De ")),50))

[mehmetcik]

Try this formula in C2 and then Fill down

I have written it this way so that it can handle names like De Giant, La Giant and De La Giant

=RIGHT(A2,LEN(A2)-FIND("$",SUBSTITUTE(A2," ","$",LEN(SUBSTITUTE(SUBSTITUTE(A2," La "," La_")," De "," De_"))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," La "," La_")," De "," De_")," ","")))))

[row2000]

Plaese try at
B2
=LEFT(A2,LEN(A2)-LEN(C2)-1)

C2
=TRIM(RIGHT(IF(ISERR(SEARCH(" de ",A2)),SUBSTITUTE(A2," ",REPT(" ",50)),SUBSTITUTE(PROPER(A2)," De ",REPT(" ",50)&"De ")),50))

This one works really great, thank you! Is there any way to also add another variation? Meaning we get the "de" variations, I'd also like to add "D'la" because it's reported like that often as well. So something like de or d'la or etc etc.

[Bo_Ry]

Please provide more sample with manually calculated results.

[row2000]

Updated sample data.

[Bo_Ry]

Plaese try at
B2
=LEFT(A2,LEN(A2)-LEN(C2)-1)

C2
=TRIM(RIGHT(IF(ISNA(LOOKUP(0,-SEARCH({" de ","d'la"},A2))),SUBSTITUTE(A2," ",REPT(" ",50)),SUBSTITUTE(SUBSTITUTE(PROPER(A2)," De ",REPT(" ",50)&"De ")," D'La ",REPT(" ",50)&"d'la ")),50))

[XOR LX]

Hi,

=MID(A2,-LOOKUP(1,-SEARCH(" "&{"ω","de","de la","d'la"}&" ",SUBSTITUTE(A2," "," ω ",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))+1,99)

Regards