Más recientemente, discutimos el uso de la función FILTER.XML para importar datos XML de Internet, la tarea principal para la que, de hecho, está destinada esta función. En el camino, sin embargo, ha surgido otro uso inesperado y hermoso de esta función: para dividir rápidamente el texto pegajoso en celdas.
Digamos que tenemos una columna de datos como esta:
Por supuesto, por conveniencia, me gustaría dividirlo en columnas separadas: nombre de la empresa, ciudad, calle, casa. Puedes hacer esto de muchas maneras diferentes:
- Uso Texto por columnas de la pestaña Datos (Datos — Texto a columnas) y dar tres pasos analizador de texto. Pero si los datos cambian mañana, tendrás que repetir todo el proceso nuevamente.
- Cargue estos datos en Power Query y divídalos allí, y luego vuelva a cargarlos en la hoja, y luego actualice la consulta cuando cambien los datos (que ya es más fácil).
- Si necesita actualizar sobre la marcha, puede escribir algunas fórmulas muy complejas para encontrar comas y extraer el texto entre ellas.
Y puedes hacerlo de forma más elegante y usar la función FILTER.XML, pero ¿qué tiene que ver?
La función FILTER.XML recibe como argumento inicial un código XML: texto marcado con etiquetas y atributos especiales, y luego lo analiza en sus componentes, extrayendo los fragmentos de datos que necesitamos. El código XML generalmente se parece a esto:
En XML, cada elemento de datos debe estar encerrado en etiquetas. Una etiqueta es un texto (en el ejemplo anterior es gerente, nombre, beneficio) encerrado entre corchetes angulares. Las etiquetas siempre vienen en pares: apertura y cierre (con una barra inclinada añadida al principio).
La función FILTER.XML puede extraer fácilmente el contenido de todas las etiquetas que necesitamos, por ejemplo, los nombres de todos los administradores y (lo más importante) mostrarlos todos a la vez en una lista. Entonces, nuestra tarea es agregar etiquetas al texto fuente, convirtiéndolo en código XML adecuado para el análisis posterior por parte de la función FILTER.XML.
Si tomamos la primera dirección de nuestra lista como ejemplo, entonces necesitaremos convertirla en esta construcción:
Llamé a la apertura global y al cierre de todas las etiquetas de texto. t, y las etiquetas que enmarcan cada elemento son s., pero puede usar cualquier otra designación, no importa.
Si eliminamos las sangrías y los saltos de línea de este código, completamente, por cierto, opcionales y agregados solo para mayor claridad, entonces todo esto se convertirá en una línea:
Y ya se puede obtener con relativa facilidad desde la dirección de origen reemplazando las comas con un par de etiquetas. usando la función SUSTITUIR (SUSTITUTO) y pegado con el símbolo & al principio y al final de las etiquetas de apertura y cierre:
Para expandir horizontalmente el rango resultante, usamos la función estándar TRANSPORTE (TRANSPONER), envolviendo nuestra fórmula en ella:
Una característica importante de todo este diseño es que en la nueva versión de Office 2021 y Office 365 con soporte para matrices dinámicas, no se requieren gestos especiales para la entrada, solo ingrese y haga clic en Participar – la fórmula en sí ocupa la cantidad de celdas que necesita y todo funciona a la perfección. En versiones anteriores, donde aún no había matrices dinámicas, primero deberá seleccionar una cantidad suficiente de celdas vacías antes de ingresar la fórmula (puede hacerlo con un margen), y después de crear la fórmula, presione el atajo de teclado Ctrl+Shift+Participarpara ingresarlo como una fórmula matricial.
Se puede usar un truco similar cuando se separa el texto pegado en una celda a través de un salto de línea:
La única diferencia con el ejemplo anterior es que en lugar de una coma, aquí reemplazamos el carácter de salto de línea invisible Alt + Enter, que se puede especificar en la fórmula usando la función CHAR con el código 10.
- Las sutilezas de trabajar con saltos de línea (Alt + Enter) en Excel
- Dividir texto por columnas en Excel
- Reemplazo de texto con SUSTITUCIÓN