Mais recentemente, discutimos o uso da função FILTER.XML para importar dados XML da Internet – a principal tarefa para a qual essa função, de fato, se destina. Ao longo do caminho, no entanto, outro uso inesperado e bonito dessa função surgiu – para dividir rapidamente o texto em células.
Digamos que temos uma coluna de dados como esta:
Claro, por conveniência, gostaria de dividi-lo em colunas separadas: nome da empresa, cidade, rua, casa. Você pode fazer isso de várias maneiras diferentes:
- Use Texto por colunas da guia Data (Dados — Texto para colunas) e dar três passos Analisador de texto. Mas se os dados mudarem amanhã, você terá que repetir todo o processo novamente.
- Carregue esses dados no Power Query e divida-os lá e, em seguida, carregue-os de volta na planilha e atualize a consulta quando os dados forem alterados (o que já é mais fácil).
- Se você precisar atualizar rapidamente, poderá escrever algumas fórmulas muito complexas para encontrar vírgulas e extrair o texto entre elas.
E você pode fazer isso de forma mais elegante e usar a função FILTER.XML, mas o que isso tem a ver com isso?
A função FILTER.XML recebe como argumento inicial um código XML — texto marcado com tags e atributos especiais, e então o analisa em seus componentes, extraindo os fragmentos de dados de que precisamos. O código XML geralmente se parece com isso:
Em XML, cada elemento de dados deve ser colocado entre tags. Uma tag é algum texto (no exemplo acima é manager, name, profit) entre colchetes angulares. As tags sempre vêm em pares – abrindo e fechando (com uma barra adicionada no início).
A função FILTER.XML pode extrair facilmente o conteúdo de todas as tags de que precisamos, por exemplo, os nomes de todos os gerenciadores e (o mais importante) exibi-los todos de uma vez em uma lista. Assim, nossa tarefa é adicionar tags ao texto fonte, transformando-o em código XML adequado para posterior análise pela função FILTER.XML.
Se pegarmos o primeiro endereço da nossa lista como exemplo, precisaremos transformá-lo nesta construção:
Eu chamei a abertura global e o fechamento de todas as tags de texto t, e as tags que enquadram cada elemento são s., mas você pode usar qualquer outra designação – não importa.
Se removermos recuos e quebras de linha deste código – completamente, a propósito, opcional e adicionado apenas para maior clareza, tudo isso se transformará em uma linha:
E já pode ser obtido com relativa facilidade a partir do endereço de origem, substituindo vírgulas nele por algumas tags usando a função SUBSTITUTO (SUBSTITUTO) e colando com o símbolo & no início e no final das tags de abertura e fechamento:
Para expandir o intervalo resultante horizontalmente, usamos a função padrão TRANSP (TRANSPOR), envolvendo nossa fórmula nela:
Uma característica importante de todo esse design é que na nova versão do Office 2021 e Office 365 com suporte para arrays dinâmicos, nenhum gesto especial é necessário para entrada – basta entrar e clicar em Entrar – a própria fórmula ocupa o número de células de que precisa e tudo funciona com um estrondo. Nas versões anteriores, onde ainda não havia matrizes dinâmicas, você precisará primeiro selecionar um número suficiente de células vazias antes de inserir a fórmula (você pode com uma margem) e depois de criar a fórmula, pressione o atalho de teclado Ctrl+Shift+Entrarpara inseri-lo como uma fórmula de matriz.
Um truque semelhante pode ser usado ao separar o texto preso em uma célula por meio de uma quebra de linha:
A única diferença com o exemplo anterior é que em vez de uma vírgula, aqui substituímos o caractere invisível de quebra de linha Alt + Enter, que pode ser especificado na fórmula usando a função CHAR pelo código 10.
- As sutilezas de trabalhar com quebras de linha (Alt + Enter) no Excel
- Dividir texto por colunas no Excel
- Substituindo texto por SUBSTITUTE