Dividir texto adhesivo coa función FILTER.XML

Máis recentemente, discutimos o uso da función FILTER.XML para importar datos XML de Internet, a principal tarefa para a que, de feito, está destinada esta función. Ao longo do camiño, con todo, apareceu outro uso inesperado e fermoso desta función: para dividir rapidamente o texto pegajoso en celas.

Digamos que temos unha columna de datos como esta:

Por suposto, por comodidade, gustaríame dividilo en columnas separadas: nome da empresa, cidade, rúa, casa. Podes facelo de varias maneiras:

• Usar Texto por columnas dende a pestana Data (Datos: texto en columnas) e vai tres pasos Analizador de texto. Pero se os datos cambian mañá, terás que repetir todo o proceso de novo.
• Cargue estes datos en Power Query e divídeos alí, e despois cárgueos de novo na folla e, a continuación, actualice a consulta cando cambien os datos (o que xa é máis sinxelo).
  
• Se precisa actualizar sobre a marcha, pode escribir algunhas fórmulas moi complexas para atopar comas e extraer o texto entre elas.

E podes facelo con máis elegancia e utilizar a función FILTER.XML, pero que ten que ver con iso?

A función FILTER.XML recibe como argumento inicial un código XML: texto marcado con etiquetas e atributos especiais, e despois analízao nos seus compoñentes, extraendo os fragmentos de datos que necesitamos. O código XML adoita ser algo así:

En XML, cada elemento de datos debe estar encerrado en etiquetas. Unha etiqueta é un texto (no exemplo anterior é xestor, nome, beneficio) encerrado entre corchetes angulares. As etiquetas sempre veñen por parellas: abríndose e pechando (cunha barra inclinada engadida ao principio).

A función FILTER.XML pode extraer facilmente o contido de todas as etiquetas que necesitamos, por exemplo, os nomes de todos os xestores, e (o máis importante) mostralos todos á vez nunha lista. Polo que a nosa tarefa é engadir etiquetas ao texto fonte, converténdoo en código XML axeitado para a súa posterior análise mediante a función FILTER.XML.

Se tomamos como exemplo o primeiro enderezo da nosa lista, necesitaremos convertelo nesta construción:

Chamei á etiqueta global de apertura e peche de todo o texto t, e as etiquetas que enmarcan cada elemento son s., pero podes usar calquera outra denominación, non importa.

Se eliminamos sangrías e saltos de liña deste código, completamente, por certo, opcional e engadido só para claridade, entón todo isto converterase nunha liña:

E xa se pode obter con relativa facilidade do enderezo de orixe substituíndo as comas por un par de etiquetas utilizando a función SUBSTITUTO (SUBSTITUTO) e pegando co símbolo & ao comezo e ao final das etiquetas de apertura e peche:

Para ampliar horizontalmente o rango resultante, usamos a función estándar TRANSP (TRANSPOSICIÓN), envolvendo a nosa fórmula nela:

Unha característica importante de todo este deseño é que na nova versión de Office 2021 e Office 365 con soporte para matrices dinámicas, non son necesarios xestos especiais para a entrada; basta con introducir e facer clic en Entrar – a propia fórmula ocupa o número de celas que necesita e todo funciona cun estrondo. Nas versións anteriores, onde aínda non había matrices dinámicas, primeiro terás que seleccionar un número suficiente de celas baleiras antes de introducir a fórmula (pode con unha marxe) e despois de crear a fórmula, prema o atallo de teclado Ctrl+Desprazarse+Entrarpara introducilo como fórmula matricial.

Pódese usar un truco similar ao separar o texto pegado nunha cela mediante un salto de liña:

A única diferenza co exemplo anterior é que en lugar dunha coma, aquí substituímos o carácter invisible de salto de liña Alt + Intro, que se pode especificar na fórmula usando a función CHAR co código 10.

• As sutilezas de traballar con saltos de liña (Alt + Intro) en Excel
• Divide o texto por columnas en Excel
• Substituíndo texto por SUBSTITUTE