Excel

Encuentra y reemplaza varios valores

Find Replace Multiple Values

Fórmula de Excel: busque y reemplace varios valoresFó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:

|_+_|

donde 'encontrar' 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 de inmediato. Por supuesto, no hay ningún requisito para usar INDICE, puede codificar valores en la fórmula si lo prefiere.



Explicación

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

|_+_|

'Texto' es el valor entrante, 'buscar' es el texto a buscar y 'reemplazar' es el texto por 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 denominado 'reemplazar'. La función INDICE se usa para recuperar tanto el texto 'buscar' como el texto 'reemplazar' como este:



cómo aplicar una fórmula a varias celdas en Excel
|_+_|

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

|_+_|

En total, ejecutamos cuatro sustituciones independientes, y cada SUSTITUTO subsiguiente comienza con el resultado del SUSTITUTO anterior:

cómo excluir celdas en blanco en Excel
|_+_|

Saltos de línea para mejorar la legibilidad

Notará 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:



|_+_|

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:

Se agregaron saltos de línea en la barra de fórmulas para facilitar la lectura y el mantenimiento

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, podría usar un adecuada tabla de Excel 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 .

Autor Dave Bruns


^