Voltar para: Excel avançado

Padronização de dados de texto

Funcionalidades usadas nesta atividade:

• Função ARRUMAR.
• Função CONCAT.
• Função DIREITA.
• Função ESQUERDA.
• Função LOCALIZAR.
• Função NÚM.CARACT.
• Função PRI.MAIÚSCULA.
• Função SEERRO.
• Função SUBSTITUIR.
• Função VALOR.

Você recebeu uma planilha com dados obtidos em um formulário de cadastro publicado na Internet. Várias pessoas responderam o formulário e os dados chegaram até você em formatos variados. Sua tarefa é padronizar os dados usando funções de texto do Excel. A planilha inicial é esta:

Uma olhada rápida na planilha nos mostra vários problemas como:

• Existem espaços desnecessários no começo, no meio e no fim dos textos, o que desorganiza a ordenação alfabética.
• As datas de nascimento foram digitadas em vários formatos, ora usando barra, ora usando hífen, etc.
• O CPF foi digitado com padrões diferentes: alguns com espaços, outros com pontos, etc.
• Algumas pessoas usaram somente minúsculas, outras só maiúsculas e o padrão que queremos é o de primeiras letra maiúscula.
• Os nomes foram digitados completos, mas precisamos separar nome e sobrenome.
• O endereço, ao contrário veio dividido em parte principal e complemento. Precisamos do endereço inteiro em uma só célula.
• Dados de data, CPF e número de telefone estão em formato texto e precisamos converte-los em números.

Vamos resolver os problemas um a um usando funções de texto do Excel. Criaremos planilhas auxiliares, cada uma vai padronizar um dos campos da planilha de dados brutos. Posteriormente, você poderá usar estas planilhas para limpar e padronizar outros dados de texto.

Nome

1. Crie uma planilha auxiliar para fazermos a padronização dos nomes de pessoas.
2. Copie os nomes da planilha de dados brutos e cole na primeira coluna da planilha nova.
3. Crie uma coluna à direita dos nomes e chame de Remover espaços.
4. Na primeira linha dessa coluna insira a função ARRUMAR.
5. O argumento da função é a célula que traz o nome despadronizado.
6. Copie a função para as demais células da coluna.

A função ARRUMAR vai  remover os espaços desnecessários no começo, no meio e no fim do texto. Vamos em frente.

1. Crie uma coluna à direita e chame de Remover caracteres indesejados.
2. Na primeira célula da coluna insira a função SUBSTITUIR.
3. O texto que vai ser tratado é o que está na célula do lado esquerdo.
4. Em texto antigo digite a sequência de texto que deseja remover. Exemplo: “.”
5. Em texto novo digite a sequência que vai tomar o lugar do texto removido. Em nosso caso, vamos usar texto vazio: “”.
6. Confirme a função e copie para as demais células para ver o efeito.

Usaremos a função SUBSTITUIR para remover caracteres como pontos e vírgulas que não nos interessam. É possível usar função composta: substituição dentro da substituição. Veja como fica a função completa:

=SUBSTITUIR(SUBSTITUIR(B2;”,”;””);”.”;””)

Próxima etapa: Padronizar o uso de maiúsculas e minúsculas.

1. Crie uma coluna à direita das demais e chame de Primeira maiúscula.
2. Na primeira célula da coluna insira a função PRI.MAIÚSCULA.
3. O texto a ser modificado é o que está imediatamente à esquerda.
4. Confirme a função e copie para as demais células.

Veja que a função PRI.MAIÚSCULA escreve todas as palavras da célula com maiúscula na primeira letra e minúscula nas demais. O resultado é quase perfeito se não fosse pelo detalhe de algumas palavras que em nossa ortografia não devem ser colocadas em maiúsculas mesmo em nomes próprios. São preposições como e, de, da, do, das e dos. Isso nos obriga a reaplicar a função SUBSTITUIR para converter De em de, Da em da, Dos em dos e etc.

1. Crie uma coluna nova e chame de Tratar exceções.
2. Na primeira célula insira uma função SUBSTITUIR.
3. Monte a função usando aninhamento e considere todas as substituições necessárias.
4. Confirme a função e copie para as demais células da coluna.

A função SUBSTITUIR neste caso fica bem longa. Veja o resultado:

=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(D2; ” Iii”; ” III”);” Jr”;” Jr. “);” Dos “;” dos “);” Do “;” do “);” Das “;” das “);” Da “;” da “);” E “;” e “);” De “;” de “)

Separação de nome e sobrenome

Agora que o nome completo está padronizado podemos fazer a separação de nome e sobrenome, um em cada célula.

A lógica para o nome é a seguinte: Consideramos nome o texto que inicia pelo lado esquerdo e avança até encontrarmos o primeiro espaço na sequência de texto da célula.

1. Crie a coluna Primeiro espaço à direita das demais.
2. Na primeira célula insira a função LOCALIZAR.
3. O texto procurado é o caractere espaço.
4. A procura será na célula do lado esquerdo.
5. O argumento número é 1, pois queremos a primeira ocorrência do caractere.
6. Confirme e copie para as demais células.

Essa coluna auxiliar nos informa em que posição está o primeiro caractere espaço no nome completo. Em seguida, vamos determinar o número total de caracteres do nome completo.

1. Crie a coluna Número de caracteres à direita da tabela.
2. Na primeira célula insira a função NUM.CARACT.
3. O texto a ser contado é o da célula à esquerda.

Com os dois valores disponíveis podemos fazer a separação do nome na cadeia de texto nome completo.

1. Crie a coluna Nome à direita das demais.
2. Na primeira célula da coluna nova insira a função ESQUERDA.
3. O texto deve vir da célula que contém o nome completo padronizado.
4. O número de caracteres a extrair é encontrado na coluna Primeiro espaço -1.
5. Confirme a função e copie para as demais células.

A função pode apresentar erro se o nome completo tiver apenas uma palavra. Para contornar o problema, usaremos a função SEERRO. Edite as funções da coluna nome para encapsular a função ESQUERDA dentro de uma função SEERRO. O resultado fica assim:

=SEERRO(ESQUERDA(E2;F2-1);E2)

Para extrair o sobrenome da sequência de texto usaremos a função DIREITA.

1. Crie a coluna Sobrenome à direita da tabela.
2. Na primeira célula, insira a função DIREITA.
3. O texto será buscado na célula do nome completo padronizado.
4. O número de caracteres é dado pela diferença entre o número total de caracteres – número de caracteres até o primeiro espaço.
5. Confirme e copie a função para as demais células da coluna.

Pronto. Agora temos nome e sobrenome padronizados e separados.

CPF

A padronização do CPF é mais simples. A primeira ação a tomar é remover os caracteres indesejados como espaços, hífens e pontos. Fazemos isso com o comando SUBSTITUIR. A função fica assim:

=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A2;”.”;””);”-“;””);” “;””)

Na sequência, temos que transformar os valores em formato texto para número. A função que faz isso é VALOR. É uma função simples de argumento único.  Lembre que não estamos verificando se o CPF é genuíno, mas apenas fazendo a padronização da apresentação.

Endereço

Os textos que compõem o endereço completo devem passar por um tratamento inicial similar ao que fizemos com os nomes completos. Você pode até copiar as mesmas fórmulas usadas na outra planilha.

A última etapa será juntar as duas partes do endereço em uma célula única. Usaremos a função CONCAT  que é bem simples. Basta informar as duas células que serão concatenadas.

Para um resultado mais satisfatório, porém, é necessário cuidar de alguns detalhes. Temos que colocar uma vírgula seguida de espaço entre as duas partes do endereço. Isso é feito digitando ” ,”& antes da segunda célula da função.

Outro cuidado a tomar é no caso de não haver complemento ou de haver apenas hífen indicando que o complemento é nulo. Aí entra uma função SE que verifica se o complemento está vazio.  Se estiver, não será preciso adicionar vírgula no meio da concatenação. A fórmula completa fica assim:

=CONCAT(G2;SE(OU(H2=””;H2=”-“);””;”, “&H2))

Cidade

As células de cidade devem ser padronizadas da mesma forma que fizemos para outros campos como endereço e nome completo. Use as funções ARRUMAR, PRI.MAIÚSCULA  e SUBSTITUIR.

Estado

Siga os mesmos procedimentos adotados para os outros campos e padronize o texto dos nomes de estados. Na sequência,  crie uma coluna para UF e faça a correspondência entre nome do estado e sua UF usando uma função PROCV.

Você pode resolver também o caso da pessoa que digitou apenas Rio em vez de Rio de Janeiro. Basta incluir a palavra Rio como uma variação para Rio de Janeiro na lista de UFs.

Telefone

A padronização do telefone envolve remover caracteres indesejados como espaços, parênteses, hífens, etc. A função SUBSTITUIR que fará isso fica assim:

=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A2;”X”;””);”)”;””);”+”;””);”(“;””);”-“;””);” “;””)

Um detalhe sobre os número de telefone é que vamos remover dígitos iniciais que não interessam como código DDI e o zero inicial de ligações interurbanas. Fazemos isso extraindo dez caracteres a partir da direita.