Blog

Postado em em 29 de outubro de 2020

Fórmula EXT TEXTO e PROCURAR para criar uma Ferramenta Poderosa

Fórmula EXT TEXTO e PROCURAR

Na publicação de hoje vamos juntar a fórmula EXT TEXTO e PROCURAR para criar uma ferramenta poderosa de análise de servidores de e-mail!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Quando utilizar a fórmula EXT TEXTO e PROCURAR?

Essa é uma combinação de fórmulas que permite a extração de partes de um texto, algumas pessoas têm algumas dúvidas em relação a isso, pois nem sabem que essa função existe, então é comum a seguinte pergunta: “Como extrair partes de um texto no Excel?”.

É possível fazer essa extração com a função EXT.TEXTO no Excel, pois ela permite com que o usuário consiga informar em que célula se encontra, onde se inicia esse texto e quantos caracteres ele possui.

Desta forma é possível fazer essa extração sem que o usuário tenha que fazer procedimentos manuais, ou seja, além de extrair uma parte do texto é possível automatizar esse processo.

A função PROCURAR no Excel é similar a função EXT.TEXTO, mas o que ela faz é procurar por uma informação e retornar à posição em que essa informação se encontra, ou seja, é possível saber a exata posição daquela informação.

Vamos utilizar essas duas funções juntas quando precisarmos extrair uma parte específica de um texto no qual não temos uma posição fixa, como é o exemplo dos e-mails por exemplo.

Caso o usuário queira saber qual o servidor de cada e-mail ele terá que analisar o que vem depois do @, só que como a informação que vem antes não tem um tamanho fixo conseguimos combinar as duas fórmulas PROCURAR e EXT.TEXTO para extrair exatamente a informação desejada.

Como utilizar a fórmula EXT TEXTO e PROCURAR?

Vamos então aprender a utilizar a função PROCURAR para que possamos encontrar pontos de referência para a extração de um texto específico.

Lista de e-mails

Lista de e-mails

Neste caso a ideia é que na coluna B possamos ter de forma automática a informação do servidor de cada um dos e-mails sem que o usuário precise fazer isso de forma manual.

Para isso é que vamos utilizar as funções PROCURAR e EXT.TEXTO, assim teremos as informações de serviço de cada e-mail independente do tamanho de e-mail que está sendo analisado.

Para iniciar teremos que criar algumas colunas auxiliares para que possamos obter as posições de certas informações que servirão de referência para a extração do texto. Por exemplo, sabemos que o servidor de cada e-mail vem logo após o símbolo de @, portanto a primeira coluna auxiliar será para procurar esse símbolo e analisar a sua posição.

Fórmula PROCURAR

Fórmula PROCURAR

Para isso vamos utilizar a função PROCURAR que tem 2 argumentos, o valor ou informação que está sendo procurada e o local onde estamos procurando essa informação.

Fórmula PROCURAR completa

Fórmula PROCURAR completa

A fórmula que teremos é bem simples, pois queremos encontrar a posição do @ na célula A2.

OBS: O símbolo de @ está entre aspas duplas, pois é considerado como um texto. E como todo texto dentro do Excel, ele tem que estar entre aspas duplas para que o programa o reconheça como texto e não número ou outra função.

Feito isso teremos a posição do @ para o primeiro e-mail, ou seja, considerando que cada letra ou símbolo representa uma posição dentro do e-mail, teremos a posição exata dessa informação.

Resultado da fórmula PROCURAR

Resultado da fórmula PROCURAR

Isso quer dizer que o símbolo de @ no e-mail joao.maia@hotmail.com ocupa a décima posição. O usuário pode contar manualmente para garantir que a informação está correta.

Com isso podemos estender a fórmula para os outros e-mails, assim teremos a posição de cada @ em cada um dos e-mails que é o primeiro passo para a extração do servidor de cada um deles, pois como foi informado o servidor vem logo após o @.

Primeira coluna auxiliar completa

Primeira coluna auxiliar completa

Como o tamanho (em letras) de cada servidor também não é o mesmo não podemos finalizar aqui e partir para a função EXT.TEXTO, nós temos que encontrar o tamanho total desse servidor para que possamos extrair a informação correta sem nenhuma informação a mais ou a menos.

Outra informação que temos é que o servidor de cada e-mail começa após o @ e termina em um ponto (.), no entanto em alguns e-mails nós temos o ponto para separar o próprio e-mail, por isso que vamos utilizar a posição do @ para iniciar a nossa busca.

Desta forma vamos evitar a obtenção de um ponto que esteja na separação do nome e acabe atrapalhando na obtenção correta das informações. Isso quer dizer que vamos começar a busca pelo ponto a partir do @, então o próximo ponto será exatamente o que delimita o nosso servidor.

Fórmula PROCURAR para o ponto

Fórmula PROCURAR para o ponto

Temos a mesma estrutura de busca que fizemos para o @, no entanto vamos utilizar a posição desse símbolo para iniciar a nova busca.

Colunas auxiliares completas

Colunas auxiliares completas

Com isso teremos a posição do @ e do ponto logo após esse símbolo para delimitar o nosso servidor. Com essas informações já seremos capazes de extrair o servidor de cada e-mail utilizando a função EXT.TEXTO.

Fórmula EXT.TEXTO

Fórmula EXT.TEXTO

Essa função também é muito simples, precisamos da célula onde temos o texto, da posição inicial onde essa extração irá começar e da quantidade de caracteres que serão extraídas.

Fórmula para extração do servidor

Fórmula para extração do servidor

A fórmula completa seria dessa maneira, para o primeiro argumento temos a célula A2 que é onde temos o e-mail. Para o segundo argumento teremos a posição do @ + 1, pois não queremos que o símbolo seja incluído na informação de servidor.

Por fim no terceiro e último argumento temos a subtração das duas posições para saber quantos caracteres tem o servidor – 1, pois precisamos também descontar a posição do ponto.

Desta forma não vamos trazer nem o @ nem o ponto para a nossa extração, teremos somente o nome do servidor que é a informação desejada.

Resultado da extração do servidor

Resultado da extração do servidor

Agora basta estender a fórmula para as demais células, que já vão pegar de forma automática as posições para cada um dos e-mails e assim extrair somente o nome do servidor de cada um desses e-mails.

Extração de todos os servidores de e-mails

Extração de todos os servidores de e-mails

Foi possível então com a utilização das fórmulas PROCURAR e EXT.TEXTO obter a informação de servidor de determinados e-mails de forma automática, pois eles não possuem tamanhos fixos.

Com o que aprendemos não seremos capazes de buscar somente servidores, mas qualquer tipo de informação dentro de uma célula que precise ser extraída para construir ou complementar um relatório. Dessa forma o usuário pode criar colunas auxiliares para obter essas posições e a partir delas extrair as informações desejadas.

Veja que é possível por exemplo extrair a parte inicial do e-mail sem o servidor. Poderia ser um tipo de login para aquela pessoa, então pegaríamos do primeiro caractere até a posição do @ – 1 para não o incluir na extração.

Sabendo de tudo isso agora basta partir para a prática seja em planilhas pessoais ou do trabalho para que consiga melhorar ainda mais seus relatórios e criar a extração de informações automática.

Caso queira saber mais sobre as fórmulas de texto temos outros posts que podem ajudar:

https://www.hashtagtreinamentos.com/formulas-de-texto-esquerda-procurar-e-ext-texto-aplicacao-das-funcoes-de-texto-no-excel/

https://www.hashtagtreinamentos.com/separar-textos-no-excel/

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?


Vai começar o maior evento de Power BI da América Latina! Quer participar? Preencha seu e-mail abaixo: