Manipulação de Texto - excel

 

Introdução

Embora o Excel não seja um editor de texto, ele possui ferramentas incríveis para análise e extração de texto. Uma célula pode comportar até 32.767 caracteres, e normalmente este limite é mais que suficiente.
O objetivo deste artigo é fornecer exemplos de operações com funções que tratam texto (strings) e são utilizadas frequentemente.

Código ASCII de um Caractere
Todo caracter possui um código ASCII (American Standard Code for Information Interchange). Esse código, para caracteres comuns, normalmente é fixo entre todas Aplicações e todas plataformas. Se por algum motivo você precisar saber o código ASCII de um caractere, use:
=CÓDIGO("B")
O resultado será 66.
Se quiser fazer o caminho inverso, isto é, descobrir qual é o caractere a partir do código ASCII, use:
=CARACT(66)
O resultado será, obviamente, B.

Verificar se Duas Células são Iguais
Existem dois métodos: um que ignora se caracteres estão em maiúsculo ou minúsculo e outro que leva em conta caracteres maiúsculo e minúsculos. Veja o exemplo abaixo:


A função de comparação (A1=B1) ignora maiúsculas de minúsculas e retorna verdadeiro se o texto for igual. No entanto, a função EXATO só retorna VERDADEIRO se os textos foram exatamente iguais, inclusive letras maiúsculas de minúsculas.

Exibição de Valores Personalizada

Números
Veja a situação abaixo:

A fórmula em B3 é
="Estou te devendo a quantia de " & A1
Seria bem melhor se, ao invés de 123456, mostrasse R$ 1.234,56. Para tal, escreva:
="Estou te devendo a quantia de " & TEXTO(A1;"R$ #.#,00")
(se quiser saber sobre regras para formatação personalizada de números, me mande um e-mail)
O resultado final:

Nota: para esse caso particular, em que a formatação desejada é de moeda, poderia usar também:
="Estou te devendo a quantia de " & MOEDA(A1)
Dessa forma, o Excel usará a formatação padrão de moeda para aplicar ao número.

Datas
Observe a tabela abaixo:

A fórmuma é B9 é:
="O concurso era dia " & A8
Para o Excel, as datas não passam de um número (se quiser saber mais sobre esse assunto. Então, quando a mesma é referenciada numa fórmula, o Excel não aplica a formatação padrão, de data. Para que a formatação fique igual à célula B10, use a fórmula:
="O concurso era dia " & TEXTO(A8;"dddd dd/MM/aaaa hh:mm:ss")
O artigo é de Word, mas a regra para a construção de data/horário é a mesma.

Repetir Caractere ou Palavra num Texto
Caso deseje repetir um caractere ou um sequência de caracteres, use a função REPT:

A fórmula é:
=REPT("Felipe ";3)
3 corresponde ao número de vezes que se deseja repetir a expressão.

Remover Excesso de Caracteres e Caracteres Não-Imprimíveis num Texto
A função ARRUMAR remove excesso de espaços antes, no meio e depois de uma cadeia de caracteres. A função TIRAR remove caracteres não imprimíveis, como quebras de texto, por exemplo. Confira os exemplos abaixo:

Em A3:
=ARRUMAR(A1)
Em A7:
=TIRAR(A5)

Contar Número de Caracteres ou Palavras num Texto
A função NÚM.CARACT conta quantos caracteres uma determinada sequência de texto possui. Note que espaços também são contados:

Em A2:
=NÚM.CARACT(A1)
As subseções a seguir mostram um uso mais elaborado da fórmula. Note que algumas funções são explicadas somente adiante.

Contar Número de Caracteres Específicos num Texto
Observe a figura abaixo:

A fórmula abaixo conta a quantidade de e presente no texto Felipe Costa Gualberto e retorna, corretamente, 3.
=NÚM.CARACT(A7)-NÚM.CARACT(SUBSTITUIR(A7;"e";""))
Explicação: subtrai-se o número total de caracteres pelo número de caracteres de quando se substitui e por vazio (em outras palavras, quando se eliminam os e). O resultado dessa expressão é a quantidade de e que foram removidos do texto original.

Contar Número de Palavras num Texto
A fórmula abaixo conta quantas palavras possui um texto:

A fórmula é:
=NÚM.CARACT(A19)-NÚM.CARACT(SUBSTITUIR((A19);" ";""))+1
A explicação é simples: conta-se quantos espaços em branco o texto possui, e em seguida adiciona-se 1 (porque, numa frase, o número de palavras é o número de espaços mais um).
No entanto, preste atenção: a fórmula retornará 1 se estiver em branco. Para corrigir esse problema, use simplesmente um SE:

=SE(NÚM.CARACT(A24)=0;0;NÚM.CARACT(A24)-NÚM.CARACT(SUBSTITUIR(A24;" ";""))+1)

Contar Número de Palavras Específicas num Texto
A diferença deste caso para o anterior é que deseja-se contar quantas palavras Costa, e não caracteres, possui dentro do texto Felipe Costa Gualberto Costa. Observe a figura abaixo:

A fórmula utilizada é:
=(NÚM.CARACT(A13)-NÚM.CARACT(SUBSTITUIR(A13;A14;"")))/NÚM.CARACT(A14)
Essa fórmula é semelhante à da subseção Contar Número de Caracteres Específicos num Texto, a diferença é que cada vez que se substitui Costa por vazio, 5 caracteres são contados a menos. Como na verdade esses 5 caracteres correspondem apenas à uma ocorrência de Costa (que possui 5 caracteres), deve-se dividir o resultado final pelo número de letras de Costa, que é 5.

Alterar Maiúsculas e Minúsculas

Existem três funções nativas do Excel para alterar letras maiúsculas e minúsculas de uma sequência de texto:
MINÚSCULA - converte todos os caracteres de uma sequência de texto para o minúsculo.
MAIÚSCULA - converte todos os caracteres de uma sequência de texto para o maiúsculo.
PRI.MAIÚSCULA - converte o primeiro caractere de cada palavra de uma sequência de texto para o maiúsculo e as outras letras em minúsculo.
Veja o exempolo a seguir:

Primeira Letra Maiúscula e Todas Outras Minúsculas

Usando funções que serão explicadas na seção a seguir, veja a fórmula abaixo:

Em A8:
=MAIÚSCULA(ESQUERDA(A7))&DIREITA(MINÚSCULA(A7);NÚM.CARACT(A7)-1)
Note que no resultado todos os caracteres são minúsculos, exceto o primeiro.
A fórmula possui duas partes: na primeira, em azul, pega-se o primeiro caractere e formata-se em maiúsculo. Em seguida, todos os outros caracteres exceto o mais à direita (ou o primeiro à esquerda) deverão ser minúsculos.

Extrair Caracteres ou Palavras de um Texto
Existem três funções nativas do Excel para extrair sequências de texto dentro de uma outra:
ESQUERDA - extrai caracteres à esquerda de um texto
DIREITA - extrai caracteres à direita de um texto
EXT.TEXTO - extrai caracteres a partir de uma determinada posição de um texto.
Veja um exemplo a seguir:

As fórmulas são:
=ESQUERDA(A1;6)
=DIREITA(A1;9)
=EXT.TEXTO(A1;8;5)
As funções ESQUERDA e DIREITA necessitam de dois parâmetros de entrada: texto a ser extraído e número de caracteres a serem extraídos (se o segundo argumento for omitido, apenas um caractere será extraído). Já a função EXT.TEXTO possui três parâmetros: texto a ser extraído, posição do caractere inicial a ser extraído e número de caracteres a serem extraídos.

Extrair a Primeira ou Última Palavra de um Texto
Veja a figura abaixo:

A fórmula para extrair a primeira palavra do texto é:
=ESQUERDA(A33;PROCURAR(" ";A33)-1)
A fórmula, através de PROCURAR, identifica qual é a posição do primeiro espaço. Extrai-se então do início do texto até uma posição antes do primeiro espaço, que explica o -1.
A fórmula para extrair a última palavra do texto é:
=DIREITA(A33;NÚM.CARACT(A33)-PROCURAR("^^";SUBSTITUIR(A33;" ";"^^";NÚM.CARACT(A33)- NÚM.CARACT(SUBSTITUIR(A33;" ";"")))))
A expressão abaixo já nos é conhecida:
PROCURAR("^^";SUBSTITUIR(A33;" ";"^^";NÚM.CARACT(A33)-NÚM.CARACT(SUBSTITUIR(A33;" ";""))))
Ela resultará em 19.
Note que a sequência ^^ é apenas um caractere temporário arbitrário, usando para fazer cálculos em cadeia.
Ela corresponde à expressão que retorna a posição da última ocorência, neste caso, de espaço. Se precisa de uma explicação mais detalhada para esta parte, veja a subseção Retornar Posição da Última Ocorrência de um Caractere num Texto. Simplificando-se a fórmula, temos:
=DIREITA(A33;NÚM.CARACT(A33)-19)
A diferença entre o número total de caracteres até o primeiro espaço corresponde à última palavra do texto.
Nota: Ambas fórmulas falharão se o texto de origem possuir apenas uma palavra. Para consertar essa situação, use as formas:
Extrair primeira palavra de texto:
=SEERRO(ESQUERDA(A39;PROCURAR(" ";A39)-1);A39)
Extrair última palavra de texto:
=SEERRO(DIREITA(A39; NÚM.CARACT(A39)-PROCURAR("*";SUBSTITUIR(A39;" ";"*";NÚM.CARACT(A39)- NÚM.CARACT(SUBSTITUIR(A39;" ";""))))); A39)
A figura abaixo ilustra a explicação acima:

Veja que quando o texto possui apenas uma palavra, as formas anteriormente explicadas de extrair a primeira ou última palavra retornam o erro #VALOR!.
A explicação da fórmula é simples: SEERRO(expressao;A39), ou seja, se a expressão retornar um erro, retorna-se o próprio texto (já que ele retorna erro quando há apenas uma palavra)

Extrair a Enésima Palavra de um Texto
A fórmula abaixo foi proposta por Frank Kabel com algumas modificações feitas por mim:

Se você usa o Excel 2003, experimente trocar na expressão abaixo o 2^31-1 por 1024:
=SE(OU(NÚM.CARACT(A47)=0;ÉERROS(PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48))));""; EXT.TEXTO(A47;PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48));SE(ÉERROS(PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48)));2^31-1;PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48))-PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48)))))
Para o caso acima, a fórmula extrai a terceira (célula A48) palavra da sequência de texto em A47. Você pode usar essa fórmula também para extrair outro delimitador sem ser o espaço, como a vírgula, por exemplo. Basta substituí-la na fórmula.

Explicação

Considere que A47 é Felipe Costa Gualberto Matos e A48 é 3. Logo, a fórmula terá que retornar Gualberto.
=SE(OU(NÚM.CARACT(A47)=0;ÉERROS(PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48))));""; EXT.TEXTO(A47;PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48));SE(ÉERROS(PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48)));2^31-1;PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48))-PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48)))))
Se a expressão em azul for verdadeira, retorna-se um texto em branco ("").
Logo, a fórmula como um todo retornará um texto em branco se NÚM.CARACT(A47)=0 ou se ÉERROS(PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48))).
NÚM.CARACT(A47)=0 é o mesmo que a expressão estar em branco, isto é, não contiver caracteres.
Em relação à expressão ÉERROS(PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48)))
Em primeiro lugar, repare que na fórmula como um todo que há substituições dee " " (um espaço em branco) em "^^". Logo depois, num nível de aninhamento anterior, converte-se novamente "^^" em " ";. Isso ocorre porque a fórmula transforma temporariamente os espaços em branco para fazer os cálculos necessários.
Em segundo lugar, veja que as expressões de busca de um termo na expressão são sempre da forma " "&A47. Esse espaço a mais é necessário porque a primeira palavra de uma frase é, na verdade, o espaço número 1. Como não existe esse espaço, temos que adicionar forçosamente na fórmula. Em outras palavras, a expressão tem apenas dois espaços, mas precisamos que tenham três, porque a palavra possui três termos. Esse espaço extra é adicionado antes de Felipe.
Logo, SUBSTITUIR(" "&A47;" ";"^^";A48) significa que na expressão " Felipe Costa Gualberto Matos" (com um espaço antes de Felipe!) será buscado o terceiro espaço (porque A48 é igual a 3) e o mesmo será substituído por ^^. A expressão se retornará então " Felipe Costa^^Gualberto Matos". Em seguida, será usada a função PROCURAR nessa expressão, buscando o termo ^^. Ora, o termo ^^ só existirá se a fórmula de SUBSTITUIR tiver sido bem sucedida. Se não tiver sido bem sucedida, retornará um erro.
Logo, da expressão
=SE(OU(NÚM.CARACT(A47)=0;ÉERROS(PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48))));""; EXT.TEXTO(A47;PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48));SE(ÉERROS(PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48)));2^31-1;PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48))-PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48)))))
A parte em azul serve apenas para verificar duas coisas:
1 - A sequência de texto tem mais de um caractere?
2 - O número do termo pesquisado (A48) é menor ou igual ao número de espaços totais presentes na sequência de texto?
Se as duas condições foram satisfeitas, então a fórmula retornará a parte em rosa, que é, reescrita com novas cores:
EXT.TEXTO(A47;PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48));SE(ÉERROS(PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48)));2^31-1;PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48))-PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48))))
Será extraído do termo azul (Felipe Costa Gualberto Matos) uma sequência de texto que inicia do caractere que é retornado pelo número verde e possui um número de caracteres igual ao valor retornado pela parte rosa.
A parte em verde já foi mostrada anteriormente: Sabemos que ela não vai dar erro aqui porque o teste condicional do aninhamento anterior passou com sucesso. Ela retornará qual é a posição onde se inicia o terceiro espaço (na verdade, a terceira ocorrência de ^^ na expressão Felipe Costa^^Gualberto Matos, que é 14. A parte em negrito (começando no G) corresponde ao 14o caractere a seguir.
Agora, precisamos que a parte rosa da fórmula nos retorne 9, que é o comprimento do termo Gualberto. Veja como ela faz isso, ao reescrevermos ela com outras cores:
SE(ÉERROS(PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48)));2^31-1;PROCURAR("^^";SUBSTITUIR(A47;" ";"^^";A48))-PROCURAR("^^";SUBSTITUIR(" "&A47;" ";"^^";A48)))
Essa expressão significa:
Se a parte em azul em for verdadeira, o número de caracteres extraídos será 2^31-1; caso contrário, será a parte rosa.
A parte em azul é idêntica à primeira expressão que combina PROCURAR com SUBSTITUIR, exceto que ao invés de se fazer a substituição no termo " "&A47, faz-se simplesmente em A47. Isso significa que retiramos o espaço extra dado à fórmula. Logo, se agora o número de espaços é um a menos que o número de termos, se procurarmos o último termo, a expressão em azul retornará um erro.
Em outras palavras, se a expressão azul retornar um erro, significa que o termo buscado é o último e, sendo o último, a função PROCURAR retorna um erro e o número de caracteres repassados à EXT.TEXTO será 2^31-1. Esse número equivale à 2147483647, que simplesmente é o número máximo de caracteres suportados pela função.
Se o termo buscado então não for o último, então será retornado o número calculado pela parte rosa. Ora esse número é simplesmente a diferença das funções de PROCURAR do A47 concatenado sem o espaço e do A47 concatenado com o espaço. Note que essa expressão daria erro se o termo buscado fosse o último, daí a necessidade de existir o teste condicional anterior.
Em outras palavras, pega-se a posição que inicia o termo n+1 e subtrai-se da posição que inicia o termo n. A diferença desses dois números é o tamanho da sequência de texto a ser extraída. No nosso caso, será feita a conta 23 - 14, que é igual a 9, o número de caracteres que Gualberto possui.

Extrair Sequência Numérica de Expressão Alfanumérica

Substituir Texto por Outro Texto
O Excel possui duas funções nativos para substituição de texto:
SUBSTITUIR - Troca um texto por outro. Utilize essa função quando souber qual sequência de caracteres será trocada.
MUDAR - Troca um texto por outro. Utilize essa função quando souber qual é a posição da sequência de caracteres a ser trocada.
Veja os exemplos a seguir:

=SUBSTITUIR(A1;"Costa";"Barriga")
=SUBSTITUIR(A1;"Costa";"Barriga";2)
A função SUBSTITUIR possui três ou quatro parâmetros. Na linha 2, foram usados três parâmetros: o texto a ser substituído, a sequência de texto a ser substituída e o texto que irá substituir. Note que todas ocorrências de Costa foram substituídas por Barriga. Na linha 3, um quarto parâmetro é utilizado (número 2), que significa que apenas a segunda ocorrência encontrada de Costa será substituída por Barriga.
Para função MUDAR:
=MUDAR(A1;8;5;"Matos")
=MUDAR(A1;14;0;"Matos ")
A função MUDAR apresenta três parâmetros: o texto a ser substituído, a posição inicial de onde começará a substituição, o número de caracteres que serão substituídos e a sequência de caracteres que entrará no lugar.
Não costumo usar muito MUDAR, e sim SUBSTITUIR. Normalmente, quando uso MUDAR, é da forma como mostrado na linha 5, quando quero adicionar uma palavra no meio de uma sequência de caracteres.

Retornar Posição de Texto Dentro de Outro Texto
O Excel possui duas funções nativas para busca de posição de um texto dentro de outro:
PROCURAR - Retorna o início da posição de uma sequência em um texto. Distingue maiúsculas de minúsculas e não suporta uso de caracteres curingas.
PESQUISAR - O mesmo que PROCURAR, exceto que PESQUISAR não distingue maiúsculas de minúsculas e suporta caracteres curingas.
Veja o exemplo abaixo, utilizando as duas funções:

=PROCURAR("Gualberto";$A$1)
=PROCURAR("gualberto";$A$1)
=PROCURAR("Gu?lb*os";$A$1)
=PROCURAR("Costa";$A$1)
=PROCURAR("Costa";$A$1;9)
PROCURAR pode possuir dois ou três parâmetros. Se forem usados dois parâmetros, o primeiro corresponde à expressão buscada e o segundo ao texto em que se busca a sequência. Caso uma sequência não seja encontrada, o erro #VALOR! é retornado.
Na linha 2, observe que Gualberto começa na posição 14 do texto. Isso justifica o valor retornado pela fórmula.
Observe (comparando linhas 2 com 3) que a função não reconhece gualberto, apenas Gualberto, pois distingue maiúsculas de minúsculas. Em outras palavras, a correspondência deve ser exata.
Na linha 4, foram utilizados caracteres curingas. Um erro foi retornado porque PROCURAR interpreta sequências com caracteres curingas literalmente (se quiser saber sobre caracteres curingas, me envie um e-mail).
Caso seja usado um terceiro parâmetro (que se oculto, o valor padrão é 1), a sequência será buscada a partir de uma posição especificada. Como na linha 5 foi oculta, a primeira ocorrência encontrada de Costa foi no caractere 8 do texto. No entanto, conforme linha 6, se a busca começar a partir do caractere 9, tem-se outra ocorrência de Costa apenas na posição 24.
Para PESQUISAR:
=PESQUISAR("Gualberto";$A$1)
=PESQUISAR("gualberto";$A$1)
=PESQUISAR("Gu?lb*os";$A$1)
=PESQUISAR("Costa";$A$1)
=PESQUISAR("Costa";$A$1;9)
PESQUISAR (ver linhas 8 e 9) retorna 14 para os casos Gualberto e gualberto porque, diferentemente da função PROCURAR, não faz distinção entre maiúsculas e minúsculas.
Observe também que na linha 10 o valor 14, corretamente retornado, é diferente de quando se tenta usar caracteres curingas com PROCURAR (que retorna um erro).
Em relação ao comportamento das linhas 11 e 12, é semelhante ao da função PROCURAR presente nas linhas 5 e 6 da Planilha.

Retornar Posição da Última Ocorrência de um Caractere num Texto
Observe a figura abaixo:

Deseja-se saber qual é a última ocorrência de o no texto em A16.
A fórmula utilizada é:
=PROCURAR("^^";SUBSTITUIR(A16;A17;"^^";NÚM.CARACT(A16)-NÚM.CARACT(SUBSTITUIR(A16;A17;""))))
A explicação da fórmula é:
Primeira conta-se quantas ocorrências do caractere o, que é dada pela expressão:
NÚM.CARACT(A16)-NÚM.CARACT(SUBSTITUIR(A16;A17;""))
O resultado é 5. Veja a expressão a seguir:
SUBSTITUIR(A16;A17;"^^";NÚM.CARACT(A16)-NÚM.CARACT(SUBSTITUIR(A16;A17;"")))
Reescrevendo a expressão, alterando o valor da expressão conhecido para 5, temos:
SUBSTITUIR(A16;A17;"^^";5)
Dessa forma, deseja-se então extrair a quinta ocorrência da expressão por um caractere diferente do original, algo que provavelmente não vai aparecer de nenhuma forma em sua expressão, como ^^. O resultado dessa expressão será:
"Felipe Costa Gualberto Costa Gualberto C^^sta"
Finalmente a fórmula:
=PROCURAR("^^";"Felipe Costa Gualberto Costa Gualberto C^^sta")
Dessa vez, procura-se o início de ^^ na expressão em verde, encontrando-se corretamente 41.
Essa função é útil para extrair o nome de um arquivo a partir de um caminho, como pode-se ver no exemplo abaixo:

A fórmula é:
=EXT.TEXTO(A28; PROCURAR("^^";SUBSTITUIR(A28;"\";"^^";NÚM.CARACT(A28)-NÚM.CARACT(SUBSTITUIR(A28;"\"; ""))))+1; NÚM.CARACT(A28))
No exemplo anterior, é retornado a posição do caractere o. Nesse exemplo, procura-se buscar a última barra de divisão de caminho (\), que é dado pela expressão:
PROCURAR("^^";SUBSTITUIR(A28;"\";"^^";NÚM.CARACT(A28)-NÚM.CARACT(SUBSTITUIR(A28;"\"; ""))))+1
Neste caso, o valor é 16+1=17. Note que o +1 é usado porque deseja-se, futuramente, extrair o caractere imediatamente após a barra. Simplificando a fórmula, temos:
=EXT.TEXTO(A28;17;NÚM.CARACT(A28))
Essa fórmula extrai todos os caracteres do 17º caractere até o final do texto e o resultado retornado é pasta1.xlsx.

Retornar Posição de Última Ocorrência de uma Palavra num Texto
Observe a figura abaixo:

A fórmula utilizada para extrair a última ocorrência de Costa no texto é:
=PROCURAR("^^";SUBSTITUIR(A22;A23;"^^"; (NÚM.CARACT(A22)-NÚM.CARACT(SUBSTITUIR(A22;A23;"")))/NÚM.CARACT(A23)))
A explicação é semelhante à da subseção anterior. Primeiro contam-se quantas ocorrências de Costa existem no texto:
(NÚM.CARACT(A22)-NÚM.CARACT(SUBSTITUIR(A22;A23;"")))/NÚM.CARACT(A23)
O resultado da expressão é 3. Simplificando-se a fórmula, tem-se a expressão:
=PROCURAR("^^";SUBSTITUIR(A22;A23;"^^";3))
A terceira (e última) ocorrência de Costa será substituída por ^^, e por fim PROCURAR identifica a posição do início da sequência.