Como dividir texto no Excel com a função TextoDivisão. Passo-a-passo com download gratuito da planilha de exemplo e vídeo explicativo.
1. Introdução a função TextoDivisão no Excel
Em março de 2022 a Microsoft lançou 14 novas funções no Excel. Estas funções inicialmente ficaram disponíveis para os usuários assinantes do Excel na versão 365, desde que sejam ingressantes no programa Office Insider em seu canal Beta.
À época da escrita deste artigo, estas funções parecem ter nomes e descrição dos seus argumentos com uma tradução ainda inadequada. Tudo indica que haverá mudanças após a otimização das traduções e descrições.
Dentre estas novas funções, uma das mais expressivas e desejadas há anos é a função TEXTODIVISÃO, que é tema deste artigo. Ela possui a finalidade de dividir textos com base em delimitadores, ou seja: basicamente faz o inverso da função UNIRTEXTO, que une textos, intercalados por delimitadores (quando assim definimos em seu 1º argumento).
A necessidade de dividir textos em colunas é muito recorrente nos diversos tipos de trabalhos com dados. Rotineiramente esta tarefa é resolvida com o recurso de Texto para Colunas, preenchimento relâmpago, ou ainda com fórmulas que empregam aninhamentos mais complexos de funções, a citar a função FILTROXML. Para entender como é possível separar textos com a função FILTROXML, acesse o vídeo abaixo.
Cabe comentar que o mais adequando é utilizarmos o Power Query para esta atividade, principalmente quando o volume de dados é expressivo e o trabalho cada vez mais recorrente.
Tendo isto em vista, este artigo expõe os fundamentos da função TEXTODIVISÃO e traz alguns exemplos práticos de uso.
2. A sintaxe da função TEXTODIVISÃO para dividir texto no Excel
Esta função retorna textos divididos em linhas e/ou colunas com base nos delimitadores, que separam os seus elementos. A imagem a seguir mostra a sintaxe desta função, que é formada por 5 argumentos, sendo os 2 primeiros obrigatórios e os 3 últimos optativos (entre colchetes na sintaxe da imagem a seguir).
text: é o texto que será dividido, sendo o nosso texto bruto (dado original).
col_delimiter: caractere(s) ou conjunto de caractere(s) que são os delimitadores de colunas.
row_delimiter: caractere(s) ou conjunto de caractere(s) que são os delimitadores de linhas.
ignore_empty: usado para decidir se ignoraremos ou manteremos itens vazios.
pad_with: funciona como um tratamento de erro e é utilizado para inserir um resultado desejado caso não haja um despejo completo em virtude de um dado faltante (vá até o item 3.5 para entender melhor).
P.S: por se tratar de funções ainda em abordagem beta, para testes e experimentação, alguns aspectos podem mudar, como o próprio nome da função e a descrição dos argumentos na sintaxe. Geralmente com o passar do tempo elas ganham traduções mais amigáveis.
3. Fundamentos da função TEXTODIVISÃO
Vamos tomar como base o cenário da imagem a seguir.
3.1. Dividindo o texto em colunas
Note que o hífen (-) é o delimitador dos elementos presentes nos dados. Desejamos separar o nosso dado em 4 colunas, preenchendo a tabela. Para isso, utilizaremos a função TEXTODIVISÃO e empregaremos apenas os seus 2 primeiros argumentos, dentre os 5 possíveis. Veja na imagem a seguir a fórmula inserida para obter as 4 colunas separadamente com os seus valores.
A fórmula inserida foi =TEXTODIVISÃO(B3;”-“). Nesta fórmula, B3 (no 1º argumento) é a referência fonte do texto (nosso dado a ser separado) e “-“ (no 2º argumento) é o delimitador utilizado para prover a separação.
3.2. Dividir texto em linhas
No item anterior, tendo em vista que utilizamos o delimitador de colunas (2º argumento de TEXTODIVISÃO), os dados foram divididos em colunas.
Caso desejássemos que os dados fossem divididos em linhas, bastaria ignorar o delimitador de colunas e inserir o nosso mesmo delimitador no argumento do delimitador de linhas, ou seja: em vez de utilizar o 2º argumento, usaremos o 3º argumento, como mostra a imagem a seguir.
A fórmula inserida foi: =TEXTODIVISÃO(I2;;”-“). Note que a fórmula apresenta ponto e vírgula (;) duas vezes consecutivas. Entre eles, o argumento que representa o delimitador de colunas foi ignorado.
3.3. Dividindo o texto em colunas e linhas simultaneamente
Podemos também obter linhas e colunas simultaneamente, ou seja: uma base de dados bidimensional. Para isso, precisamos tanto discriminar o delimitador de colunas quanto o delimitador de linhas.
Vamos tomar como base o dado do exemplo a seguir:
10-Vermelho,12-Preto,15-Azul,19-Amarelo
Desejamos obter 2 colunas: uma com os números (10, 12, 15 e 19) e outra com as cores (Vermelho, Preto, Azul, Amarelo). No nosso dado, o hífen (-) representa o divisor de colunas e a vírgula (,) representa o delimitador de linhas. Veja a imagem a seguir e a fórmula que resulta na correta divisão dos dados.
A fórmula inserida foi: =TEXTODIVISÃO(B3;”-“;”,”).
3.4. Ignorando vazios
Conforme exposto no item 2, o argumento ignore_empty (4º argumento da função TEXTODIVISÃO) é utilizado para ignorar vazios. Este argumento, ao assumir VERDADEIRO (que pode ser representado por 1), ignora itens vazios. Como resultado, os itens despejados ficam adjacentes, sem células em branco. Ao assumir o valor FALSO (que pode ser representado por 0), os itens despejados mantêm células vazias para os itens faltantes.
A imagem a seguir mostra dados em que algumas vezes o terceiro elemento não existe (note a vírgula duas vezes consecutivas). Veja as linhas com itens faltantes destacadas em amarelo. Este dado faltante é a sigla de uma UF, que em outros dados foi MT e GO, por exemplo.
A imagem a seguir mostra o resultado quando NÃO ignoramos os itens vazios, resultando em células vazias.
Perceba que as células das UFs ausentes estão destacadas na coluna E, em amarelo, uma vez que não ignoramos vazios. A fórmula utilizada foi =TEXTODIVISÃO(B3;”,”;;0).
A imagem a seguir mostra o resultado quando ignoramos os itens vazios, resultando em células adjacentes sem elementos vazios.
Perceba que os dados ficaram todos contínuos, sem células vazias misturadas. Entretanto, este tipo de disposição de dados não é favorável para a correta estruturação porque dados de um certo campo “invadem” espaços de outros campos. Isso ocorre na linha 4, por exemplo. Veja que a palavra Total ocupa a coluna E e em alguns casos também ocupa a coluna F, o que desfavorece a correta organização de dados. A fórmula utilizada foi =TEXTODIVISÃO(B3;”,”;;1).
3.5. Dividir texto preenchendo resultados ausentes (contornando erros)
Quando utilizamos os delimitadores de colunas e os delimitadores de linhas simultaneamente, caso algum dado esteja faltante para o completo despejo, podemos usar o último argumento de TEXTODIVISÃO para substituir o erro gerado (#N/D) por algum valor mais conveniente, contornando este problema. Isto evita a necessidade de utilizarmos a função SEERRO.
A imagem a seguir mostra um dado em que não há cor correspondente para o código 19 (todos os demais códigos possuem cores associadas). O resultado foi obtido sem o devido tratamento com o 5º argumento (pad_with).
A fórmula utilizada foi =TEXTODIVISÃO(B3;”-“;”,”).
Já a próxima imagem apresenta o tratamento com a utilização do 5º argumento. Optamos por contornar o erro, trocando-o por um texto mais amigável e significativo (Sem cor).
A fórmula utilizada foi =TEXTODIVISÃO(B3;”-“;”,”;;”Sem cor”). Vale destacar que o argumento ignore_empty foi omitido porque em nosso caso não fará diferença.
4. Casos de estudo – Aplicações para Dividir Texto
A seguir vamos estudar 3 casos de aplicações da função TEXTODIVISÃO.
4.1. Separação de dados de faturamento
Tomemos como base os dados a seguir.
Desejamos obter os dados de Data, UF, NF, Produto e Valor, respectivamente. Observando os dados, percebemos que o delimitador é o hífen (-). Para este nosso caso, a fórmula é simples: usará apenas os 2 primeiros argumentos, da mesma forma que foi exposto no item 3.1.
A imagem a seguir ilustra a fórmula inserida para dividir o 1º registro.
A fórmula inserida foi =TEXTODIVISÃO(B3;”-“). Essa fórmula deverá ser copiada para as próximas linhas para que possamos obter todos os nossos dados devidamente separados com o preenchimento.
É possível utilizar a função TEXTODIVISÃO apenas uma vez (fórmula única) e obter os resultados de todos os registros. Isto pode ser realizado com o uso acessório da função UNIRTEXTO. Acompanhe como realizar esta otimização no vídeo: https://youtu.be/1sWt3GiwObw.
A imagem a seguir ilustra a fórmula otimizada, que foi =TEXTODIVISÃO(UNIRTEXTO(“|”;;B3:B12);”-“;”|”).
4.2. Separação de dados com delimitadores diferentes para colunas
Tomemos como base os dados a seguir.
Note que os nossos delimitadores de colunas são primordialmente ponto e vírgula (;). No entanto, a cidade e a UF devem ser separadas mas estão sendo delimitadas por hífen (-), e não ponto e vírgula (;). Como desejamos separar também estes dois campos, precisamos de alguma manobra para este fim.
A boa notícia é que o argumento para delimitador de colunas (o mesmo vale para o delimitador de linhas) aceita múltiplas possibilidades. Com isso, podemos informar no 2º argumento os 2 (ou mais) delimitadores para separar todas as colunas.
A imagem a seguir ilustra a fórmula aplicada: =TEXTODIVISÃO(B3;{“;”;” – “}).
Note que o 2º argumento de TEXTODIVISÃO apresenta um vetor com seus elementos entre chaves. O 1º elemento deste vetor é o ponto e vírgula e o segundo é o hífen, precedido e sucedido por espaço. Uma fórmula alternativa que transforma todos os delimitadores em apenas um (troca “ – “ por “;”) para obter os mesmos resultados poderia ser: =TEXTODIVISÃO(SUBSTITUIR(B3;” – “;”;”);”;”).
4.3. Dividir texto extraindo o nome e último sobrenome
Tomemos como base os dados a seguir, em que precisamos obter o nome (1ª palavra) e o último sobrenome (palavra final) dos nomes completos.
Aqui, destaca-se o fato de o delimitador natural entre as palavras ser o próprio espaço existente entre elas. Assim, para o 1º registro, os resultados esperados são Afonso e Barreto, respectivamente.
Tendo em vista que TEXTODIVISÃO separa as partes do dado, podemos obter todas as partes separadas e após isso utilizar a função ÍNDICE para retornar apenas o elemento desejado, de acordo com a sua posição.
Com base nisto, para obter o 1º nome, ÍNDICE deverá retornar sempre o 1º elemento, ou seja: ÍNDICE terá em seu 1º argumento a função TEXTODIVISÃO retornando todos os elementos, e em seu 2º argumento o valor 1, indicando que desejamos o 1º elemento dentre os elementos já separados e disponibilizados pela função TEXTODIVISÃO.
A imagem a seguir aponta a fórmula utilizada, que foi =ÍNDICE(TEXTODIVISÃO(B3;” “);1).
Uma fórmula alternativa sugerida com funções mais antigas do Excel: =ESQUERDA(B3;LOCALIZAR(” “;B3)-1). Outra fórmula alternativa usando a nova função TEXTOANTES: =TEXTOANTES(B3;” “).
Já para a obtenção do último sobrenome, utilizaremos a mesma lógica, com uma pequena diferença. O último elemento de TEXTODIVISÃO poderá ter posições variadas, ocupando a 2ª, 3ª, 4ª, 5ª posições etc. Com isso, precisamos informar esta posição de maneira dinâmica no 2º argumento de ÍNDICE. Esta posição, que será a última, é simplesmente o número de elementos retornados por TEXTODIVISÃO. Podemos então obter esta quantidade com a função CONT.VALORES, que terá como argumento a função TEXTODIVISÃO, idêntica àquela já inserida no 1º argumento de ÍNDICE.
A imagem a seguir apresenta a fórmula empregada.
A fórmula utilizada foi: =ÍNDICE(TEXTODIVISÃO(B3;” “);CONT.VALORES(TEXTODIVISÃO(B3;” “))). Nesta fórmula, a substituição de CONT.VALORES por COLS implicará nos mesmos resultados.
Uma fórmula alternativa sugerida com funções mais antigas do Excel: =ARRUMAR(DIREITA(SUBSTITUIR(B3;” “;REPT(” “;NÚM.CARACT(B3)));NÚM.CARACT(B3))). Outra fórmula alternativa usando a nova função TEXTODEPOIS: =TEXTODEPOIS(B3;” “;NÚM.CARACT(B3)-NÚM.CARACT(SUBSTITUIR(B3;” “;””))).
Download planilha exemplo de como dividir texto no Excel
Clique no botão abaixo para realizar o download da planilha de TextoDivisão Excel, com exemplo de dados:
