Fórmula genérica
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Resumen
Para buscar y reemplazar varios valores con una fórmula, puede anidar varias funciones SUBSTITUTE juntas y alimentar pares de buscar / reemplazar de otra tabla usando la función INDICE. En el ejemplo que se muestra, estamos realizando 4 operaciones independientes de búsqueda y reemplazo. La fórmula en G5 es:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
donde "buscar" es el rango con nombre E5: E8 y "reemplazar" es el rango con nombre F5: F8. Consulte a continuación para obtener información sobre cómo hacer que esta fórmula sea más fácil de leer.
Prefacio
No hay una fórmula incorporada para ejecutar una serie de operaciones de búsqueda y reemplazo en Excel, por lo que esta es una fórmula de "concepto" para mostrar un enfoque. El texto para buscar y reemplazar se almacena directamente en la hoja de trabajo en una tabla y se recupera con la función INDICE. Esto hace que la solución sea "dinámica": cualquiera de estos valores se cambia y los resultados se actualizan inmediatamente. Por supuesto, no es necesario utilizar INDEX; puede codificar valores en la fórmula si lo prefiere.
Explicación
En el núcleo, la fórmula usa la función SUSTITUIR para realizar cada sustitución, con este patrón básico:
=SUBSTITUTE(text,find,replace)
"Texto" es el valor entrante, "buscar" es el texto a buscar y "reemplazar" es el texto con el que reemplazar. El texto para buscar y reemplazar se almacena en la tabla de la derecha, en el rango E5: F8, un par por fila. Los valores de la izquierda están en el rango con nombre "buscar" y los valores de la derecha están en el rango con nombre "reemplazar". La función INDICE se utiliza para recuperar tanto el texto "buscar" como el texto "reemplazar" como este:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Entonces, para ejecutar la primera sustitución (busque "rojo", reemplace con "rosa") usamos:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
En total, ejecutamos cuatro sustituciones separadas, y cada SUSTITUTO subsiguiente comienza con el resultado del SUSTITUTO anterior:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Saltos de línea para facilitar la lectura
Notarás que este tipo de fórmula anidada es bastante difícil de leer. Al agregar saltos de línea, podemos hacer que la fórmula sea mucho más fácil de leer y mantener:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
La barra de fórmulas en Excel ignora los espacios en blanco adicionales y los saltos de línea, por lo que la fórmula anterior se puede pegar directamente:
Por cierto, hay un atajo de teclado para expandir y contraer la barra de fórmulas.
Más sustituciones
Se pueden agregar más filas a la tabla para manejar más pares de buscar / reemplazar. Cada vez que se agrega un par, la fórmula debe actualizarse para incluir el nuevo par. También es importante asegurarse de que los rangos con nombre (si los está utilizando) se actualicen para incluir nuevos valores según sea necesario. Alternativamente, puede usar una tabla de Excel adecuada para rangos dinámicos, en lugar de rangos con nombre.
Otros usos
El mismo enfoque se puede utilizar para limpiar el texto "quitando" la puntuación y otros símbolos del texto con una serie de sustituciones. Por ejemplo, la fórmula de esta página muestra cómo limpiar y reformatear números de teléfono.
