Fórmulas de limpeza de dados comuns no Excel

fórmulas do excel

Durante anos, usei a publicação como um recurso não apenas para descrever como fazer as coisas, mas também para manter um registro para consultar mais tarde! Hoje, tivemos um cliente que nos entregou um arquivo de dados do cliente que foi um desastre. Praticamente todos os campos foram formatados incorretamente e; como resultado, não foi possível importar os dados. Embora existam alguns ótimos complementos para o Excel para fazer a limpeza usando o Visual Basic, executamos o Office para Mac que não oferece suporte a macros. Em vez disso, procuramos fórmulas simples para ajudar. Pensei em compartilhar alguns deles aqui apenas para que outros possam usá-los.

Remover caracteres não numéricos

Os sistemas geralmente exigem que os números de telefone sejam inseridos em uma fórmula específica de 11 dígitos com o código do país e sem pontuação. No entanto, as pessoas geralmente inserem esses dados com travessões e pontos. Esta é uma ótima fórmula para removendo todos os caracteres não numéricos no Excel. A fórmula analisa os dados na célula A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Agora você pode copiar a coluna resultante e usar Editar> Colar Valores para sobrescrever os dados com o resultado formatado corretamente.

Avalie vários campos com um OU

Freqüentemente, eliminamos registros incompletos de uma importação. Os usuários não percebem que nem sempre você precisa escrever fórmulas hierárquicas complexas e que, em vez disso, pode escrever uma instrução OR. Neste exemplo abaixo, quero verificar A2, B2, C2, D2 ou E2 para dados ausentes. Se algum dado estiver faltando, irei retornar um 0, caso contrário, um 1. Isso me permitirá classificar os dados e excluir os registros que estão incompletos.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Cortar e Concatenar Campos

Se seus dados tiverem campos de nome e sobrenome, mas sua importação tiver um campo de nome completo, você pode concatenar os campos perfeitamente usando a função Concatenar embutida do Excel, mas certifique-se de usar TRIM para remover todos os espaços vazios antes ou depois de texto. Envolvemos todo o campo com TRIM no caso de um dos campos não possuir dados:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Verifique se há um endereço de e-mail válido

Uma fórmula bem simples que procura por @ e. em um endereço de e-mail:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Extraia o nome e o sobrenome

Às vezes, o problema é o oposto. Seus dados têm um campo de nome completo, mas você precisa analisar o nome e o sobrenome. Essas fórmulas procuram o espaço entre o nome e o sobrenome e capturam o texto quando necessário. A TI também trata se não houver sobrenome ou se houver uma entrada em branco em A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

E o sobrenome:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Limite o número de caracteres e adicione ...

Você já quis limpar suas meta descrições? Se você quiser puxar o conteúdo para o Excel e, em seguida, cortar o conteúdo para uso em um campo Meta Descrição (150 a 160 caracteres), você pode fazer isso usando esta fórmula de Meu lugar. Ele quebra claramente a descrição em um espaço e adiciona o ...:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Claro, eles não pretendem ser abrangentes ... apenas algumas fórmulas rápidas para ajudá-lo a começar! Que outras fórmulas você usa? Adicione-os nos comentários e eu darei o crédito enquanto atualizo este artigo.

O que você acha?

Este site usa o Akismet para reduzir o spam. Saiba como seus dados de comentário são processados.