Sustitución múltiple con fórmulas en Excel

Una cuestión planteada recurrentemente en diferentes foros de Exce es la posibilidad de realizar múltiples sustituciones con fórmulas de una sola vez:

Esta sustitución múltiple es posible empleando la función XMLFILTRO (ya vista en articulos previos de este blog), y alguna función desbordada como SECUENCIA, y otras funciones clásicas como UNIRCADENAS, INDICE, BUSCARV o SI.ERROR.
Recomendado por tanto emplear versión de Microsoft 365!!.

Veamos lo que necesitamos...
En un rango A2:A6 las cadenas de texto donde aplicar nuestra sustitución múltiple.
Una tabla 'TblREEMPLAZO' donde tengamos qué deseamos reemplazar y por qué... No importa cuan larga sea la tabla!.
Y finalmente nuestra fórmula en B2:
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1))))

O una versión más 'light':
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")))

¿Un poco larga??... Pero muy potente!

Descompongámosla...

La clave como puedes comprobar es nuevamente el uso de XMLFILTRO, función que apareció en la versión 2013, y de poco conocimiento general... pero que a dia de hoy, con el comportamiento desbordado, con las fórmulas desbordadas, ha subido a un alto status entre las funciones.

Empezaremos analizando la fórmula:
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")
que nos retornaría una vector/matriz vertical de elementos, entendiendo elementos para nuestro caso por palabras:

En un segundo paso cada elemento o palabra recuperada la pasamos con un BUSCARV sobre nuestra TblREEMPLAZO:
BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0)

En este paso hemos identificado cualquier palabra dentro de nuestra lista de reemplazos, y a la vez han sido sustituidas... Las palabras no encontradas devuelven lógicamente un #N/D.

Situación que controlamos en el siguiente paso con la función SI.ERROR:
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1)))

La idea de este paso es muy sencilla. Buscamos palabras a sustituir, y si no está en la lista nos quedamos con la palabra original.
La función INDICE combinada con SECUENCIA se encarga de retornar precisamente esa palabra no encontrada en la 'TblREEMPLAZO':
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1))

Recuerda que, en general, también se podría emplear simplemente:
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")

El último paso es el más obvio de todos, con UNIRCADENAS, juntamos cada palabra final obtenida con las fórmulas previas, y como vimos en una versión más abreviada:
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")))

Y con la fórmula acabada podemos arrastrar en B2:B6 como necesitábamos.
Todo un éxito!!.

En definitiva hemos conseguido una búsqueda y sustitución 'recursiva', evitando el uso de anidaciones de SUSTITUIR.