Problems when splitting strings of text with compound names.

[RonOliver]

Hi, everyone. I need a helping hand.

I'm trying to create a tool that splits the name and last name in a cell into three cells (one cell for the first name and the other two for both last names, as we have two last names in Spanish). I got the last names thing, but I'm still struggling with the first name because of the following:

We can add prepositions and articles to both first and last names in Spanish (for example, "de la Rosa" would be just one last name). In Spanish, you can only have two last names (apart from prepositions and articles preceding them), but you can have as many first names as you want (typically two or, very rarely, three), and they may also include prepositions (for example, María de los Ángeles is somebody's first name). My tool is able to split "Javier" / "del Pino" / "Fernández" with no problems, but it is struggling to split first names with prepositions (such as "María de los Desamparados Medina de la Rosa" or "Juan de Dios de Alcántara Bayarri"). That's why I feel my formula is useless and I need to create it from scratch, but I don't even know where to begin at this point.

I need a formula that will extract the first name, which is always going to be the full name minus the two last names and their respective prepositions. I feel like this is probably very easy, and I feel just very dumb for not getting this to work. A complete list of articles and prepositions would be: "de", "del", "de la", "de los", "de las", "el", "la", "los", "las", in case it helps. All help is very much welcome.

 

[jasonb75]

I feel just very dumb for not getting this to work.

Far from it, allowing for exceptions in formulas is often far from easy.

Have I got the examples that you provided correct? Some of the functions that I have used are quite new so you may not have them yet, if that is the case then I'm not sure that it will be possible with a formula but I will have a look and see if I can come up with anything. (The new functions used are TEXTSPLIT, TAKE, DROP, and HSTACK. If you have those then you're good to go).

Book1
	A	B	C	D
2	María de los Desamparados Medina de la Rosa	María de los Desamparados	Medina	de la Rosa
3	Juan de Dios de Alcántara Bayarri	Juan de Dios	de Alcántara	Bayarri
Sheet3

Cell Formulas
Range	Formula
B2:D3	B2	=LET(arr,SUBSTITUTE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," de la "," de-la-")," de las "," de-las-")," de los "," de-los-")," de "," de-")," del "," del-")," el "," el-")," la "," la-")," los "," los-")," las "," las-")," "),"-"," "),first,TEXTJOIN(" ",1,DROP(arr,,-2)),last,TAKE(arr,,-2),HSTACK(first,last))
Dynamic array formulas.

 

[RonOliver]

This is actually pretty amazing, to be honest. I already have the other two formulas figured out, so I guess this would delete them (or make me delete them manually). However, this works so well, apparently, that I don't even feel bad for wasting time and effort on less efficient formulas.

I'll check that thoroughly (I'm going to add an IF function at the beginning for foreign names that only have one last name, but that I can do myself, hopefully), but that's solved as far as I'm concerned. Thank you so very much! I'll try to familiarize myself with these new functions.