▷ Caracteres Especiais Estranhos no Excel

Olá pessoal sejam bem vindo ao meu novo artigo! Irei descrever neste artigo um problema frequente dentro do MS Excel. Quando estamos trabalhando com outras plataformas chamadas ERP (Enterprise Resource Planning), que são os sistemas de gestão de uma empresa, muitas vezes precisamos trabalhar com os dados exportados onde iremos iniciar as nossas análises, criando indicadores e/ou painéis estratégicos.

Devido a um vídeo postado do meu amigo Dr. Eng. João Rosa - Botão do Excel (clique aqui para ver o vídeo), decidi fazer um artigo sobre toda essa problemática que são os "caracteres especiais" e como que o MS Excel trata todos eles.

Mas precisamos entender o conceito fundamental dentro do MS Excel

Quando um valor se alinha para a esquerda ele é "Texto"

Quando um valor se alinha para a direita ele é "Número"

Mas podemos notar que a célula A2 tem o valor digitado "240" e está alinhado à esquerda. E a célula não está formatada como texto, mas sim como "Geral". Sendo que, para esta célula, não está ativado o alinhamento.

Mas porque acontece isso?

A explicação para esse fato é que o MS Excel não consegue identificar todos os valores numéricos, com isso ele mantém por padrão o formato de texto, ocasionando muitos erros, principalmente para realização de operações matemáticas ou para a busca de valores, ex: código de peças.

Para converter de "Texto" em "Número" é possível utilizar 4 métodos, que são eles:

1° - Multiplicar a célula pelo valor 1 (um)

2° - Adicionar na célula o valor 0 (zero)

3° - Dividir a célula pelo valor 1 (um)

4° - Utilizar a fórmula =VALOR() ou em inglês =VALUE()

Mas caso ainda persista o erro, com a utilização destes métodos, é porque existe um caracter não visível em sua célula, por isso irei descrever a parte técnica deste problema.

A definição é que o MS Excel trabalha com o Padrão de caracteres chamado ASCII - 7 bits - ANSI (segue a referência) que resulta na tabela abaixo,

Onde temos do caracter 1 ao 255, sendo que para visualizar o caracter "B" devemos digitar a fórmula abaixo no MS Excel:

=CARACT(66)

Ou em inglês

=CHAR(66)

Analisando os itens destacados em vermelho (na imagem acima), os caracteres não visíveis (ou não imprimíveis), faço a separação em três classificações conforme descrevo abaixo:

1° - Caracteres que inserem um espaço

São os códigos 32 (espaço), 160 e 2

2° - Caracteres ocultos

São os códigos 9, 13, 28, 29, 31, 129, 141, 143, 144 e 157

3° - Caracter do "enter" (combinação de tecla de atalho Alt+Enter)

O código 10 (enter)

Para a realizar a "limpeza" destes caracteres dos nossos textos teremos que utilizar uma das três fórmulas abaixo ou elas combinadas.

1° - SUBSTITUIR/SUBSTITUE

Esta na minha opinião é a melhor fórmula para retirar esses caracteres indesejáveis, utilizando a fórmula abaixo

=SUBSTITUIR(B15;CARACT(160);" ")

ou em inglês

=SUBSTITUTE(B15;CHAR(160);" ")

2° - TIRAR/CLEAN

Pela definição da Microsoft, a fórmula TIRAR/CLEAN remove os seguintes caracteres de número 127, 129, 141, 143, 144 e 157, que são classificados como "Caracteres Não Imprimíveis" e infelizmente não retira o caracter 160.

3° - ARRUMAR/TRIM

Esta fórmula ela remove os espaços (caracter 32) antes e depois das palavras ou espaços indevidos entre os textos.

Estratégias para identificação dos "Caracteres Não Imprimíveis"

Devido a esses detalhes dos caracteres classificados como "Caracteres Não Imprimíveis" criei a seguinte fórmula para identificar em qual posição está o caracter problemático, detalhe que esta fórmula só irá funcionar para apenas "um caracter" diferente.

O valor correto foi inserido na célula B2, na célula B3 o valor com o problema.

Para identificar qual caracter está com o problema segue a fórmula na Célula B5

=SOMA(ÍNDICE((EXT.TEXTO(B2;LIN(INDIRETO("1:"&AB36));1)<>EXT.TEXTO(B3;LIN(INDIRETO("1:"&AB37));1))*LIN(INDIRETO("1:"&AB36));0))

ou em inglês

=SUM(INDEX((MID(B2;ROW(INDIRECT("1:"&AB36));1)<>MID(B3;ROW(INDIRECT("1:"&AB37));1))*ROW(INDIRECT("1:"&AB36));0))

Com a posição identificada, a fórmula abaixo irá resultar em qual caracter está a divergência entre os textos, segue a fórmula na célula B6 e B7

=CÓDIGO(EXT.TEXTO(B2;B5;1))

ou em inglês

=CODE(MID(B2;B5;1))

Para substituir um caracter pelo outro segue a fórmula na Célula B9

=SUBSTITUIR(B3;CARACT(B7);CARACT(B6))

ou em inglês

=SUBSTITUTE(B3;CHAR(B7);CHAR(B6))

Identificação Caracter a Caracter

Neste caso, quando é necessário identificar mais do que um caracter segue o desenvolvimento abaixo para verificar qual posição contém o erro.

Segue a fórmula nas Células M3 a AF4

=SEERRO(EXT.TEXTO($K3;COLS($M$3:M3);1);"")

ou em inglês

=IFERROR(MID($K3;COLUMNS($M$3:M3);1);"")

A verificação do erro está na Célula M5 até AF5, que segue abaixo

=SE(M4="";"";IF(EXATO(M3;M4);"Ok";"Erro"))

ou em inglês

=IF(M4="";"";IF(EXACT(M3;M4);"Ok";"Erro"))

Após a verificação de onde está o erro, segue a fórmula nas Células M7 a AF8

=SE(OU(M$5="";M$5="Ok");"";SEERRO(CÓDIGO(M3);""))

ou em inglês

=IF(OR(M$5="";M$5="Ok");"";IFERROR(CODE(M3);""))

Dica Bônus

O famoso CARACT(10) - "Enter"

Para deixar elegante os textos dentro da caixa de texto será necessário concatenar dois valores com um "Enter", para isso devemos inserir uma fórmula que auxiliará em todo esse processo que está na célula K29, segue abaixo:

=SE(K26=0;M22;SE(K26>0;M23;M24))&TEXTO(K26;"0,0%")&CARACT(K19)
&K28&TEXTO(K3;"#.###")

ou em inglês

=IF(K26=0;M22;IF(K26>0;M23;M24))&TEXT(K26;"0,0%")&CHAR(K19)
&K28&TEXT(K3;"#.###")

Note que quando você finaliza a fórmula o MS Excel não irá mostrar o "Enter" entre os textos. Para visualizar, deverá criar uma caixa de texto, ir na barra de fórmula, digitar o "=" (igual) e depois selecionar a célula K29 e apertar o "Enter", com isso o texto concatenado estará vinculado a caixa de texto, onde é possível mudar o valor, e o mesmo será alterado dentro da caixa de texto.

Espero que tenha gostado desse artigo!

Caso tenha alguma sugestão de outra técnica, poste aqui nos comentários.

Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do Excel.

E ja conhece o meu novo curso online de Excel?

Abraços a todos e até o próximo artigo!

Fabio BALDINI

Frase do dia:"O melhor jeito de prever o futuro é inventá-lo" Autor: Alan Kay

OBS: Quero agradecer imensamente ao Grande Mestre e Amigão Alessandro Trovato pelas eternas revisões dos artigo para que sempre fique esmero. Obrigadão meu amigo!