Separar Nombres y Apellidos solo con fórmulas

Un tema que siempre me ha interesado es cómo separar eficazmente los nombres y apellidos compuestos en las tres partes correspondientes: Nombre + Apellido 1 + Apellido 2
Creo que de manera bastante exitosa lo conseguí con macros aquí y con Power Query aquí.

En el día de hoy conseguiremos lo mismo con la tercera pata de Excel, vamos a a separar nombre y apellidos compuesto solo con fórmulas!!.

El algortimo de cálculo empleado ha sido el mismo que el usado en programación o con Power query, i.e., identificar y separar las partes con algún separador (la barra vertical | en mi caso), para después aplicar y separar por dichos separadores...
Puedes releer los links a los artículos comentados para aclarar el concepto.

Para ello emplearemos la función web XMLFILTRO, vista en los artículos del blog previos a este. Y en especial haremos uso de la técnica expuesta en este post, donde explico cómo realizar sustituciones múltiples con fórmulas.
Nuevamente el uso desbordado de nuestras funciones matriciales, en versiones modernas de Excel (especialmente Microsoft 365) nos permitirá completar con éxito esta tarea.

Habitualmente en mis posts expongo en primer lugar la fórmula completa para luego ir descomponiendola... hoy lo haré a la inversa, mostraré los cálculos celda a celda, paso a paso... para finalmente recomponer todas ellas en un único cálculo.

Empecemos...
En primer lugar tenemos nuestro nombre compuesto (o no) completo en la celda A2.
Y nuestro primer paso, nuestra primera fórmula en D2 sería:
=SUSTITUIR(NOMPROPIO(A2);" ";"| ")
Donde homogeneizamos el texto del nombre con la función NOMPROPIO, esto es, cada primera letra de cada palabra en mayúscula... para luego cambiar los espacios intermedios por una barra vertical seguida de otro espacio en blanco...

En el paso siguiente aplicamos el proceso de sustitución múltiple comentado en un post previo.
Así en la celda D3 insertamos:
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblReemp;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")))
donde se procede a sustituir cada elemento o palabra del nombre por su equivalencia de la tabla de reemplazos... si no existiera equivalencia nos quedamos con la palabra original.

El siguiente punto consiste en un simple conteo de partes del resultado anterior, en D4 aplicamos la fórmula:
=LARGO(D3)-LARGO(SUSTITUIR(D3;"|";""))+1

Si todo va bien hasta ahora, como máximo podremos tener cuatro partes...
Por tanto lo que haremos será recuperar, por separado, cada una de ellas. Para lo cual haremos uso, de nuevo, de la función XMLFILTRO con un uso curioso de su segundo argumento, donde indicaremos qué elemento/posición queremos obtener...
Entonces, como pasos siguientes tendremos en D5:
=TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D$3;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[1]"))
Notemos como recuperamos el primer elemento con //ini/fila/dato[1]
En D6 para el segundo elemento:
=TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D$3;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[2]"))
En D7:
=TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D$3;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[3]"))
Y en D8 para el último:
=TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(D$3;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[4]"))

Y llegamos al resultado final, empleando los pasos recorridos, en D9 insertamos:
=SI(D4>3;
TRANSPONER(XMLFILTRO("<ini><fila><dato>"&UNIRCADENAS("</dato><dato>";FALSO;D5&" "&D6;SI.ERROR(D7;"");SI.ERROR(D8;""))&"</dato></fila></ini>";"//ini/fila/dato"));
TRANSPONER(XMLFILTRO("<ini><fila><dato>"&UNIRCADENAS("</dato><dato>";FALSO;D5;SI.ERROR(D6;"");SI.ERROR(D7;""))&"</dato></fila></ini>";"//ini/fila/dato")))
Donde controlamos y decimos que si hay cuatro partes, necesariamente es por que el nombre es compuesto, y concatenamos las partes/elementos de las celdas D5,D6,D7 y D8 de acuerdo a esas partes.
Para evitar posibles fallos, en caso de que no existan tantas partes (por ejemplo un nombre y un solo apellido, o algo similar), aplicamos la función SI.ERROR a estas partes...

Con lo que concluiríamos nuestro trabajo... podemos probar diferentes combinaciones de nombre y comprobaríamos que el éxito esta garantizado en un muy elevado porcentaje, cercano al 100%.

Pero... ¿y si quiero trabajar todo esto en una sola fórmula?
No te preocupes... si has comprendido los pasos explicados, entenderás enseguida la fórmula siguiente.
Emplearemos la función LET (ver aquí), que nos permite construir fórmulas 'monstruosas' agilmente, y fáciles de seguir...

Veamos nuestra 'megafórmula' aplicada sobre diferentes combinaciones de nombres compuestos o simples...

En la celda D15 añadimos:
=SI.ERROR(LET(Paso0;SUSTITUIR(NOMPROPIO(A15);" ";"| ");
Paso1;UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso0;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblReemp;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso0;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")));
Partes;LARGO(Paso1)-LARGO(SUSTITUIR(Paso1;"|";""))+1;
part1;TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso1;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[1]"));
part2;TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso1;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[2]"));
part3;TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso1;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[3]"));
part4;TRANSPONER(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(Paso1;"|";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato[4]"));
SI(Partes>3;
TRANSPONER(XMLFILTRO("<ini><fila><dato>"&UNIRCADENAS("</dato><dato>";FALSO;part1&" "&part2;part3;SI.ERROR(part4;""))&"</dato></fila></ini>";"//ini/fila/dato"));
TRANSPONER(XMLFILTRO("<ini><fila><dato>"&UNIRCADENAS("</dato><dato>";FALSO;part1;SI.ERROR(part2;"");SI.ERROR(part3;""))&"</dato></fila></ini>";"//ini/fila/dato"))));"")

Puedes copiar o arrastrar la fórmula al resto de nombres y comprobar su correcto funcionamiento.