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

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!

Resposta rápida: a função EXT.TEXTO extrai um pedaço de um texto a partir de uma posição — =EXT.TEXTO(texto; núm_inicial; núm_caracteres) — e a função PROCURAR devolve a posição em que um caractere ou palavra aparece no texto — =PROCURAR(texto_procurado; dentro_do_texto; [núm_inicial]). Juntas, elas extraem trechos que não têm posição fixa. Por exemplo, para pegar o domínio de um e-mail (o que vem depois do “@”): =EXT.TEXTO(A2; PROCURAR("@";A2)+1; 100).

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

O que você aprende neste vídeo

Resposta rápida: Para tirar só o servidor de cada e-mail, o nome entre o arroba e o ponto seguinte, a aula mostra o preenchimento relâmpago errando em alguns nomes e então monta duas colunas auxiliares com PROCURAR, uma com a primeira letra depois do arroba e outra com o ponto seguinte, cuja diferença vira o tamanho no EXT.TEXTO.

Neste vídeo (20 min):

• 1:33 - Com Hotmail e Gmail digitados nas duas primeiras linhas, o preenchimento relâmpago (Ctrl + E) completa a lista, mas Yahoo, Terra e Microsoft vêm com o ponto com junto.
• 4:22 - Em EXT.TEXTO, cada letra, número ou espaço conta uma posição: no e-mail de A2, o arroba é a décima, e começar ali com 3 caracteres devolve o arroba e as duas letras seguintes.
• 6:26 - Começando na posição 11 com 7 caracteres, sai hotmail; puxada para baixo, a mesma fórmula vira bagunça, porque o servidor de cada e-mail começa numa posição diferente.
• 8:34 - Na coluna D, PROCURAR com hotmail, entre aspas, procurado em A2, devolve 11.
• 9:32 - Com o arroba no lugar de hotmail, a fórmula devolve 10 e, puxada para baixo, a posição do arroba em cada e-mail, como 13, 14 e 6.
• 10:34 - Com =PROCURAR("@";A2)+1, a coluna D passa a apontar a primeira letra do servidor e substitui o 11 fixo do EXT.TEXTO: todos começam no lugar certo, e o problema que sobra é o tamanho, que muda de servidor para servidor.
• 13:17 - Na coluna E, outra PROCURAR busca o ponto, mas acharia sempre o primeiro do e-mail, e no e-mail do João esse ponto separa João de Maia, antes do arroba.
• 15:12 - Com o número inicial da PROCURAR em 10, a busca começa no arroba, pula o primeiro ponto e acha o da posição 18.
• 15:45 - O número inicial passa a ser a coluna auxiliar D, e a coluna E, puxada até o fim, acha em todos os e-mails o primeiro ponto depois do arroba.
• 17:00 - No EXT.TEXTO da coluna B, o número de caracteres vira a posição do ponto menos a da primeira letra (18 menos 11 no e-mail do João), e o duplo clique preenche o servidor de todos os e-mails.
• 18:21 - Sem o mais um na coluna D, a conta muda: o início vira a posição do arroba mais 1, e o tamanho, 18 menos 10 menos 1.

Trechos do vídeo:

• PROCURAR devolve a posição da primeira letra do que se procura, contada sempre a partir do começo do texto, mesmo quando a busca começa mais adiante pelo número inicial.
• Para achar o ponto que fecha o servidor, procure só o ponto, e não o ponto com, porque nem todo e-mail tem ponto com (há ponto net); e, como pode haver ponto antes do arroba, comece a busca depois dele pelo número inicial da PROCURAR.
• Se o número de caracteres do EXT.TEXTO passar do fim do texto, a extração vai só até o último caractere escrito.

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.

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.

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.

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.

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 @.

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.

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.

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.

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.

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.

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.

Foi possível então com a utilização da fórmula EXT TEXTO e PROCURAR 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-excel

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

Hashtag Treinamentos

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

---

---