8 funções no Excel para trabalhar com dados de texto 35 Comentários



Trabalhar com dados de texto (strings) no Microsoft Excel pode, realmente, ser uma dor de cabeça! Nomes que precisam ser separados, rótulos que contém espaços no início e final, números e códigos que "somem" com alguns caracteres no meio do processo. Por este motivo, este artigo abaixo trata de relacionar, descrever e dar exemplos práticos da rotina dos profissionais: os desafios que encontram, erros sem "solução" no Excel e formas de resolvê-los.

Abaixo descrevo oito funções que podem, efetivamente, solucionar muitas necessidades do usuário no Microsoft Excel, ao trabalhar com manipulação de textos e formatação de valores:

Função DIREITA e ESQUERDA

As duas funções vão retornar os caracteres desejados a contar da esquerda ou da direita.

Sintaxe

=DIREITA('texto';'[núm_caract]') ou =ESQUERDA('texto';'[núm_caract]'), onde:

  • 'texto': Representa o texto, a partir dos qual serão extraídos os caracteres desejados.
  • '[núm_caract]': Representa a quantidade de caracteres que serão extraídos do texto, a partir da esquerda ou da direita.

Em um exemplo, vamos supor que temos uma base de produtos que contém um código de 5 dígitos iniciais mais o nome do item.

Figura 1: Utilizando a função ESQUERDA para extrair dados de texto.

Figura 1: Utilizando a função ESQUERDA para extrair dados de texto.

oitoFun2

Figura 2: Utilizando a função DIREITA para extrair dados de texto.

 Obrigatoriamente, sabe-se que existem 5 caracteres no início do texto que podem ser extraídos. Então, inserimos a fórmula =ESQUERDA(A2;5) na célula A2 e arrastamos para baixo (figura 1). O código é extraído e agora você poderá trabalhá-lo mais consistentemente!

E se o código estive após o texto, na direita? Usamos a mesma lógica, mas com a função DIREITA (figura 2).

Função NÚM.CARACT

A função retorna a quantidade de caracteres contidas em um texto ou célula e é útil para realizar algum tipo de validação de dados, preparação de arquivo para carga em banco de dados e utilização combinada com outras funções para extrair um texto específico.

Figura 3: Contagem de caracteres no texto com a função NÚM.CARACT

Figura 3: Contagem de caracteres no texto com a função NÚM.CARACT

Sintaxe

=NÚM.CARACT('texto'), onde 'texto' é uma célula apontada ou um texto sobre o qual deseja contar a quantidade de caracteres.

A função é de utilização simples mas pode ser aplicada de forma combinada para potencializar sua utilidade. No exemplo da figura 3, apenas demonstramos o resultado simples da função. Você pode ter estipulado, por exemplo, que os textos não podem passar de 20 caracteres e utilizar a fórmula para checar se há linhas que extrapolam a condição.

Função ARRUMAR

Esta função é de grande auxílio na rotina, principalmente para aqueles que trabalham com informações externas em planilhas, arquivos de texto ou extraídos de banco de dados e sistemas. Sua aplicação é remover "espaços extras" que podem aparecer no início, entre ou final de sequências de texto, respeitando os espaços únicos entre palavras.

Sintaxe

=ARRUMAR('texto'),  onde 'texto' é uma célula apontada ou um texto sobre remover espaços extras.

Figura 4: Remover "espaços extras" com a função ARRUMAR

Figura 4: Remover "espaços extras" com a função ARRUMAR

Vamos supor que você extrai uma base de clientes de um banco de dados e percebe que os nomes vêm com espaços extras no início e no final do nome, além de alguns registros com espaços triplos entre o sobrenome, como apontado na figura 4. Esta "desorganização de dados" atrapalha sempre uma consolidação e cruzamento de informações - e às vezes o usuário pode nem notar os espaços extras - onde nada encontra nada. Inserimos a fórmula ARRUMAR na célula B2 e arrastamos para baixo. Note como os espaços indesejados são automaticamente removidos!

Função LOCALIZAR

A função LOCALIZAR retorna um número que representa a posição do texto procurado em uma sequência de texto ou célula, por exemplo, na sequência de texto "ÁGUA.GALÃO", a posição do texto procurado "." (ponto) será 5.

Sintaxe

=LOCALIZAR('texto_procurado';'no_texto','[núm_inicial]'), onde:

  • 'texto_procurado': representa o texto que você deseja encontrar sua posição na sequência;
  • 'no_texto': representa a sequência de texto ou célula apontada que contém o texto procurado;
  • '[núm_inicial]': representa a posição na sequência a partir da qual deseja iniciar a busca. É opcional e, se omitido, o Excel considerará automaticamente como '1', ou seja, localizando a partir da primeira letra da sequência de texto.
Figura 5: Encontrar posição de caractere com função LOCALIZAR

Figura 5: Encontrar posição de caractere com função LOCALIZAR

Para esta demonstração, olhamos para uma lista de jogadores de basquete onde temos todas as informações na coluna A, separadas por um traço ("-"). O primeiro dado é o número da camisa, o segundo é o nome e o terceiro a posição na quadra. Desejamos encontrar a posição do segundo traço, antes da posição na quadra. Na figura 5, inserimos a fórmula LOCALIZAR("-";A2;4).

Sabemos que o número da camisa nunca será maior que 99, então temos 3 caracteres, contando com o traço ao final: 4 é a posição inicial para realizarmos a busca. Em outras situações, onde a posição inicial pode variar, não podemos utilizar um valor fixo, mas variável. Assim, podemos utilizar uma função LOCALIZAR dentro da outra: =LOCALIZAR("-";A2;LOCALIZAR("-";A2)+1).

A segunda função aninhada ';LOCALIZAR("-";A2)+1' retornará a posição do primeiro traço mais um, que servirá de base para a primeira função LOCALIZAR.

Função SUBSTITUIR

A função SUBSTITUIR, como o próprio nome diz, irá substituir um texto por outro texto, dentro da sequência de texto ou célula apontada. Útil para remover caracteres especiais, como pontos e traços e pode ser utilizada, como todas as funções no Excel, de forma aninhada.

Sintaxe

=SUBSTITUIR('texto';'texto_antigo'; 'novo_texto'; '[núm_da_ocorrência]'), onde:

  • 'texto': representa a sequência de texto ou célula apontada que contém o texto que deseja alterar.
  • 'texto_antigo': representa o texto que deverá ser alterado.
  • 'novo_texto': representa o texto que substituirá o 'texto_antigo'.
  • '[núm_da_ocorrência]'): representa a ordem da ocorrência de poderá ser substituída. É opcional, se não for especificada, o Excel substituirá todas as ocorrências do 'texto_antigo'. Você poderá especificar o valor '3', por exemplo, para alterar apenas a 3ª ocorrência do texto antigo na ordem da sequência de texto.
Figura 6: Substituir texto com a função SUBSTITUIR

Figura 6: Substituir texto com a função SUBSTITUIR

No exemplo da figura 6, queremos remover todos os caracteres especiais de uma só vez de uma base de CNPJs, então vamos utilizar SUBSTITUIR de forma aninhada, ou seja, uma função dentro da outra. Inicialmente a fórmula fica assim: =SUBSTITUIR(A2;".";""), trocamos todos os pontos por vazio (removendo-os). Então "substituímos a substituição" de tal forma que a fórmula final é: =SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A2;".";"");"-";"");"/";"")

Função TEXTO

A função TEXTO permite retornar um valor específico ou contido em uma célula em uma formatação personalizada. Por exemplo, números podem ser convertidos em um formato de data e hora, monetário ou científico.

Sintaxe

=TEXTO('valor';'formato_texto'), onde:

  • 'valor': representa o valor ou célula a ser convertida ou formatada de forma personalizada.
  • 'formato_texto': representa o formato de "texto" que deverá ser retornado para a fórmula.
Figura 7: Formatando valores nas células com a função TEXTO.

Figura 7: Formatando valores nas células com a função TEXTO.

A função TEXTO é bastante flexível e permite criar qualquer tipo de formatação. Para formatar um número em notação data-hora, por exemplo, utilize =TEXTO(4141;"DD/MM/AAAA HH:MM:SS") - onde D (dia), M (mês), A (ano), H (hora), M (minutos), S (segundos).

Vamos considerar a mesma base de CNPJs, com alguns que começam com 0. Já passou por esse problema? Eles podem ser automaticamente convertidos para valor e perder o zero à esquerda. Para consertar isso, inserimos a função =TEXTO(A2;"00000000000000"), arrastamos para baixo (figura 7) e agora os zeros devem aparecer!

A mesma metodologia deve funcionar para outros tipos de dado, como CPF, RG etc.

Função EXT.TEXTO

A função EXT.TEXTO permite extrair um texto específico de uma sequência de texto em qualquer local: no início, meio ou fim. Basta especificar onde começa e onde termina sua extração. A função é útil para separar blocos de texto que estão agregados e onde deseja-se separá-los em subgrupos que possam ser trabalhados (filtrados, somados, etc).

Sintaxe

 =EXT.TEXTO('texto'; 'núm_inicial'; 'núm_caract'), onde:

  • 'texto': é o texto ou célula que contém a sequência da qual serão extraídos os blocos desejados;
  • 'núm_inicial': representa a posição inicial de caractere na sequência de texto, a partir da qual inicia-se o bloco a extrair;
  • 'núm_caract': representa a quantidade de caracteres a partir de 'núm_inicial' que delimitam o bloco a extrair e onde ele termina.

Vamos considerar a nossa base contendo a listagem de jogadores de basquete, onde desejamos separar as informações de número da camisa, nome e posição em colunas separadas.

Figura 8:

Figura 8: Extraindo textos com função EXT.TEXTO, primeira coluna.

Primeiro, utilizamos a função LOCALIZAR nas colunas B e C para identificar a posição na sequência do primeiro traço e do segundo traço. A primeira coluna (número da camisa) pode ser extraída considerando o 'núm_inicial' como '1' (afinal é o primeiro item da sequência) e 'núm_caract' como o valor na coluna B - 1, ou seja, a posição do primeiro traço menos 1 (por que não queremos o traço na extração). A função inserida na coluna para número da camisa deverá ficar como demonstrado na figura 8.

A segunda coluna que deverá conter o nome do jogador utilizará a função com os critérios a seguir (figura 9): 'núm_inicial' deverá ser a coluna B mais um, ou seja, a posição do primeiro traço mais um, uma vez que não queremos exibir o primeiro traço. 'núm_caract' deverá ser a coluna C menos a coluna B (significa a quantidade de caracteres que existem entre o primeiro traço e o segundo traço) menos um (por que não queremos exibir o segundo traço).

Por fim, para a última coluna (posição), demonstrado na figura 10: 'núm_inicial' deverá ser a coluna C mais um, ou seja, a posição do segundo traço mais um, uma vez que não queremos exibir o segundo traço e o 'núm_caract' será a função =NÚM.CARACT(Ax) (contagem de caracteres totais da sequência de texto) menos a coluna C, resultando na quantidade de caracteres restantes para o final da sequência.

Abaixo, obtenha a planilha contendo os exercícios no link abaixo:

down_icon Download | Exercícios - Funções de Texto

Se gostou compartilhe clicando nos botões abaixo e participe deixando um comentário com críticas, sugestões, complementação etc!

27,147 total views, 7 views today


Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

35 pensamentos em “8 funções no Excel para trabalhar com dados de texto

  • Dayvid

    Boa noite,

    Preciso extrair todos os valores (um em cada coluna) que vem depois do “:”

    {“idVeiculo”:840624,”dataPosicao”:”2015-11-21 00:54:21.0″,”dataPacote”:”2015-11-20 22:47:14.0″,”latitude”:-1.9648766666666666,”longitude”:-48.170415,”direcao”:0,”velocidade”:0,”ignicao”:1,”odometro”:211404,”horimetro”:161082,”tensao”:27,

    Sendo que a informação que esta em “” precisaria reconhecer como nome da coluna.

    Podes me ajudar por gentileza?

    View Comment
  • Jefferson Pereira

    Ola,

    Possua uma planilha com apenas uma coluna e com valores diferentes e cada célula.
    Preciso excluir toda célula que possua menos de 8 Dígitos, assim restando a penas as que possuem 8 ou mais dígitos.

    Tem como realizar este processo.

    Desde já obrigado.

    View Comment
    • Paulo Campos
      Paulo Campos Autor do post

      Jefferson, Olá! Tudo bem? Verifique a função NÚM.CARACT neste mesmo artigo. Ele contará a quantidade de caracteres da célula. Depois utilize o AutoFiltro para filtrar apenas as células com menos de 8 caracteres. Ao final, é só excluir as linhas inteiras. O Excel irá remover somente estas que estão filtradas.
      Espero ter ajudado.
      Forte abraço,

      View Comment
  • André

    Paulo, preciso de uma ajuda, em uma planilha tenho a seguinte linha em uma célula
    [{“currency_id”:”BRL”,”item”:{“id”:”xxxxxxxxxxxxxx”,”title”:”xxxxxxxxxxxxxxxxxxxxxl”,”seller_custom_field”:”12345-1″,”variation_attributes”:[],”category_id”:”xxxxxxxxxxxx”,”variation_id”:null},”sale_fee”:2.08,”quantity”:1,”unit_price”:13,”xxxxx_id”:”5769a1235be5afba255ba53a”,”sku”:”12345-1″}]

    Em uma outra planilha eu puxo todos os dados que preciso, cada um em sua respectiva célula, usando procurar, Ext.text, Direita, esquerda, etc… .é fácil pois são tamanhos padrões. meu problema é que um dos dados que preciso nem smepre vai ter um tamanho de caracteres padrão para extrair ele completo no caso o código 12345-1 ele varia no tanho de caraceres antes do ” -” e depois. ele pode ser por exemplo 12345-21/2, por isso não consigo extrair ele da forma correta, como posso fazer isso? uma vez que tudo que eu preciso está dentro das aspas.

    View Comment
    • Paulo Campos
      Paulo Campos Autor do post

      Ana,
      Na célula B2 (por exemplo) insira a fórmula =DIREITA(A2,1) (onde A2 é a célula que contém o nome da pessoa).
      Na célula em seguida, insira a fórmula =SE(B2=”a”;”Senhora”;”Senhor”).
      No entanto, não creio que possa funcionar para todos os casos. Pense no nome “Tamires”…
      Abraços

      View Comment
  • Ariovaldo de Almeida

    Boa tarde, tenho pouca experiencia em Excel e estou precisando de uma planilha para consulta dos valores das Custas Cartorárias em alguns Estados, onde eu possa, a partir de informações como abaixo, digitar em um campo o Estado e o valor do título, e saber o valor das Custas. Ex:
    ESTADO-VALOR TÍTULO – CUSTAS
    DF — ATÉ R$ 200,00 – R$ 32,75
    DF — ATÉ R$ 450,00 – R$ 75,32
    DF — ACIMA DE R$ 450,00 -R$ 132,15
    GO — ATÉ R$ 250,00 – R$ 43,28
    GO — ATÉ R$ 600,00 – R$ 79,80
    GO — ACIMA DE 600,00 – R$ 147,75
    PE — ATÉ R$ 100,00 – R$ 12,75
    PE — ATÉ R$ 200,00 – R$ 22,75 + 0,20% do valor titulo.
    PE — ACIMA DE 200,00 – R$ 42,15 +0,20% do vr do titulo.

    OBS: Gostaria que esses dados ficassem ocultos, abrindo apenas uma célula para eu digitar o Estado,e outra para digitar o valor do título que pode ser qualquer valor de R$ 100,00 até R$ 30.000,00. Em uma outra célula aparecesse o valor das Custas.

    View Comment
  • Hirto Júnior

    Paulo, parabéns pelo site.
    Gostaria de sua ajuda ou de outro visitante do site.
    Tenho uma pasta de trabalho com diversas planilhas.
    Eu digito textos somente na primeira planilha e ele replica o texto em determinadas células das outras planilhas.
    Meu problema é que o texto da primeira planilha recebe negrito em algumas palavras, mas em outras não. Isso é para destacar aquele trecho na hora da leitura para uma Sessão de Julgamento. Eu precisaria que nas células das outras planilhas fosse mantida também a formatação da célula em que digitei. Enfim, mesmo que eu negrite somente uma parte do texto da primeira célula, esse negrito somente nessa parte também seja repetido na célula que referencia a original (replicar texto e formatação).
    Enfim, gostaria de saber se há uma fórmula ou algo para resolver isso.

    View Comment
    • Paulo Campos
      Paulo Campos Autor do post

      Olá Hirto! Obrigado!
      Infelizmente não há uma fórmula pronta para replicar a formatação da célula.
      No entanto, é possível fazer isso utilizando VBA (programação).
      Se puder, compartilhar sua planilha comigo, posso incluir a programação para replicar esta formatação.
      Abs,
      Paulo Campos

      View Comment
  • Leonardo dos Santos

    Olá bom dia,
    Tenho em uma planilha uma coluna com descrição de materiais, que esta organizada em ordem alfabética, preciso gerar códigos com a primeira letra da descrição + numero.
    EX:
    A001
    A002
    B003
    Pode me ajudar?
    Desde ja lhe agradeço.

    View Comment
  • Matheus Fonseca

    Boa tarde. Tenho esses seguintes dados na mesma coluna:

    Dr. Antonio Fernando Carneiro
    Av. Francisco Sales, 1656
    Santa Efigênia, Belo Horizonte – MG
    30150-221
    Dr. Sergio de Almeida Correa
    Rua Guajajaras, 410
    Centro, Belo Horizonte – MG
    30180-100
    Dr. Leonardo Correa de Araujo
    Rua Gustavo Pena N 44, 403
    Horto, Belo Horizonte – MG
    31015-060

    Preciso separar os dados em cada coluna específica, nome/endereço/cep. Já tentei de várias maneiras, mas não consegui. Você poderia me ajudar? Obrigado.

    View Comment
    • Paulo Campos
      Paulo Campos Autor do post

      Olá Matheus, tudo bem?
      Realmente o formato como os dados estão dispostos torna muito difícil a separação.
      No entanto, se eu copiar o texto como você dispôs aí em cima e colar no Excel, consigo separar em linhas.
      A partir daí você pode transpor. Se você copiar e colar em um editor de texto e depois copiar e colar do editor para o Excel, ele te separa em linhas?
      Abraço

      View Comment
  • Bruna

    Oi Paulo. Gostaria de saber se o que eu preciso, é possível se fazer no excel. Conheço pouco!
    Espero que consiga me ajudar!

    Minha planilha do excel tem 2 abas. Na 1ª aba, tenho uma coluna com código, e uma coluna com a descrição, pra dizer ao que se refere aquele código. Gostaria de alguma função que, ao digitar o código da 1ª aba do excel, na 2ª aba, ele entenda e substitua o código pela descrição.

    Por exemplo:
    Temos na 1ª aba:

    CÓD
    012 VESTIDO ESTAMPADO
    013 SAIA LISA
    014 BLUSA VERMELHA

    Essas informações, eu só passo para a 2ª aba, quando realizo a venda das mesmas.
    Gostaria de poder, ao digitar o código 012 , o excel já transcrevesse automaticamente : ” 012 VESTIDO ESTAMPADO ”
    Sem que eu precise copiar e colar essas informações o tempo todo!

    Isso é possível?!?! Que fórmula eu uso pra isso?

    Me ajudaaa, por favor!

    View Comment
  • Cintia Cruz

    Olá preciso criar uma tabela onde dependendo do texto digitado seja realizado um calculo percentual a partir de outro valor.
    Ex. Se estiver escrito exame na celula A1 e um valor de 100,00 na celula B1 a resposta em C1 um é o valor de B1 vezes 20%, mas se em A1 for digitado consulta o valor da celula de B1 deve ser multiplicado por 50%.
    De acordo com o que se escreve o valor tem um percentual a ser calculado
    Como crio esta formula?

    View Comment
  • António Lopes

    Boa noite,
    Desde já agradeço a possível ajuda para uma situação que tenho com o excel.
    Uma folha A está esquematizada da seguinte forma:
    NIF Nome
    123456789 ABC
    ….. …..
    Em outra folha B está esquematizada assim:
    Código NIF Nome
    12345 123456789 ABC

    O que pretendo é que numa terceira folha C, esta tenha os dados da Folha B com base nos dados da Folha A
    O campo comum é o NIF e pretendo obter o código e nome,
    Obrigado

    View Comment
  • Wesley

    Preciso que o Excel me retorne o valor da célula que contenha determinado carácter por exemplo:

    Localizar as células que contenham as siglas “RV” e copiar em outra coluna.

    poderia estar me ajudando?

    View Comment
  • Daniel Deusdete

    Bom dia. Obrigado por seu site. Como faço para extrair de uma planilha somente os links nela apontados em relação a uma célula? Por exemplo, uma determinada célula tem um link e eu quero justamente esse link para compor outra tabela.

    Agradeço muito a sua atenção.

    View Comment
  • Vilmo Antonio

    Olá, tudo bem.
    Agradecimento.
    Há alguns dias estava pensando e pesquisando em vários sites como extrair os números do CNPJ individualmente para fazer a conferencia pelo digito verificador se o usuário digitou corretamente, eu tentava extrair da direita para esquerda, mas tem CNPJ que começa com zero (zero a esquerda) usando a fórmula =SEERRO(EXT.TEXTO(AA3;PROCURAR(DIREITA(AA3;1);AA3)-2;1);0) repetindo-a uma para cada numero do CNPJ, exceto os dois últimos, não sei porque alguns CNPJ não funcionaram, e ai encontrei esta função =TEXTO(A2;”00000000000000″), digitei na célula AA3 e com esta formula =1*EXT.TEXTO(AA3;12;1) resolvi meu problema, por isso que eu amo EXCEL ele faz a gente pensar, muitíssimo obrigado.

    Rotina para verificar ou descobrir o Digito Verificador do CNPJ, eu baixei da internet, me perdoe mas não lembro qual foi o site.
    VERIFICAÇÃO DO CNPJ
    Primeiro Dígito Verificador:
    Digite no campo sombreado o Nº do CNPJ até o número da filial:
    0 0 0 0 0 0 0 0 0 0 0 1
    6 7 8 9 2 3 4 5 6 7 8 9
    0 0 0 0 0 0 0 0 0 0 0 9 9 0 9

    Segundo Dígito Verificador:

    0 0 0 0 0 0 0 0 0 0 0 1 9
    5 6 7 8 9 2 3 4 5 6 7 8 9
    0 0 0 0 0 0 0 0 0 0 0 8 81 89 8 1

    CNPJ da Filial:
    0 0 0 0 0 0 0 0 0 0 0 1 9 1

    View Comment