EXCEL: Encontrar y reemplazar múltiples valores

Para buscar y reemplazar múltiples valores con una fórmula, puede anidar múltiples funciones de SUSTITUIR juntas y alimentar pares de buscar / reemplazar desde otra tabla usando la función INDICE. En el ejemplo que se muestra, estamos realizando 4 operaciones de buscar y reemplazar por separado. La fórmula en G5 es:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(B5,INDICE(find,1),INDICE(replace,1)),INDICE(find,2),INDICE(replace,2)),INDICE(find,3),INDICE(replace,3)),INDICE(find,4),INDICE(replace,4))

donde "find" es el rango con nombre E5: E8, y "replace" 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 existe una fórmula integrada 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 a 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 cambia, los resultados se actualizan de inmediato. Por supuesto, no hay ningún requisito para usar INDICE; puede codificar valores en la fórmula si lo prefiere.

Cómo funciona esta fórmula

En el núcleo, la fórmula usa la función SUSTITUIR para realizar cada sustitución, con este patrón básico:

=SUSTITUIR(text,find,replace)

"Texto" es el valor entrante, "buscar" es el texto a buscar y "reemplazar" es el texto con el que se va a reemplazar. El texto a buscar y reemplazar se almacena en la tabla a 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 usa para recuperar el texto "buscar" y el texto "reemplazar" de esta manera:

INDICE(find,1) // first "find" value
INDICE(replace,1) // first "replace" value

Entonces, para ejecutar la primera sustitución (busque "rojo", reemplace con "rosa") usamos:

=SUSTITUIR(B5,INDICE(find,1),INDICE(replace,1))

En total, ejecutamos cuatro sustituciones separadas, y cada SUSTITUIR posterior comienza con el resultado del SUSTITUIR anterior:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(B5,INDICE(find,1),INDICE(replace,1)),INDICE(find,2),INDICE(replace,2)),INDICE(find,3),INDICE(replace,3)),INDICE(find,4),INDICE(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:

=
SUSTITUIR(
SUSTITUIR(
SUSTITUIR(
SUSTITUIR(
B5,
INDICE(find,1),INDICE(replace,1)),
INDICE(find,2),INDICE(replace,2)),
INDICE(find,3),INDICE(replace,3)),
INDICE(find,4),INDICE(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á usando) 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

Se puede usar el mismo enfoque para limpiar el texto "eliminando" la puntuación y otros símbolos del texto con una serie de sustituciones. Por ejemplo, la fórmula en esta página muestra cómo limpiar y formatear números de teléfono.