Formules Excel pour le nettoyage des données courantes

Pendant des années, j'ai utilisé la publication comme une ressource non seulement pour décrire comment faire les choses, mais aussi pour garder une trace pour moi-même à consulter plus tard ! Aujourd'hui, nous avons eu un client qui nous a remis un fichier de données client qui était un désastre. Pratiquement tous les champs étaient mal formatés et ; par conséquent, nous n'avons pas pu importer les données. Bien qu'il existe d'excellents modules complémentaires pour Excel pour effectuer le nettoyage à l'aide de Visual Basic, nous exécutons Office pour Mac qui ne prend pas en charge les macros. Au lieu de cela, nous recherchons des formules simples pour vous aider. J'ai pensé en partager quelques-uns ici juste pour que d'autres puissent les utiliser. ponctuation. Cependant, les gens entrent souvent ces données avec des tirets et des points à la place. Voici une excellente formule pour supprimer tous les caractères non numériques dans Excel. La formule examine les données de la cellule 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))

Vous pouvez maintenant copier la colonne résultante et utiliser Édition > Coller les valeurs pour écraser les données avec le résultat correctement formaté.

Évaluer plusieurs champs avec un OU

Nous purgeons souvent les enregistrements incomplets d'une importation. Les utilisateurs ne réalisent pas que vous n'avez pas toujours à écrire des formules hiérarchiques complexes et que vous pouvez plutôt écrire une instruction OU. Dans cet exemple ci-dessous, je souhaite vérifier A2, B2, C2, D2 ou E2 pour les données manquantes. S'il manque des données, je vais retourner un 0, sinon un 1. Cela me permettra de trier l'ordre des données et de supprimer les enregistrements incomplets.

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

Rogner et concaténer les champs

Si vos données ont des champs Prénom et Nom, mais que votre importation a un champ de nom complet, vous pouvez concaténer les champs avec soin à l'aide de la fonction Excel Concaténer intégrée, mais assurez-vous d'utiliser TRIM pour supprimer tous les espaces vides avant ou après le texte. Nous enveloppons le champ entier avec TRIM dans le cas où l'un des champs n'a pas de données :

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

Vérifier l'e-mail valide Adresse

Une formule assez simple qui recherche à la fois le @ et le . dans une adresse e-mail :

=ET(TROUVE(“@”,A2),TROUVE(“.”,A2),ESTERREUR(TROUVE(” “,A2)))

Extraire les noms et prénoms[19659003]Parfois, le problème est le contraire. Vos données ont un champ de nom complet, mais vous devez analyser les noms et prénoms. Ces formules recherchent l'espace entre le prénom et le nom et saisissent le texte si nécessaire. Le service informatique gère également s'il n'y a pas de nom de famille ou s'il y a une entrée vide dans A2.

=SIERREUR(SI(RECHERCHE(" ",A2,1),GAUCHE(A2, RECHERCHE(" ",A2,1)),A2),SI(LEN(A2)>0,A2"") )

Et le nom de famille :

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

Limiter le nombre de caractères et ajouter …

Avez-vous déjà voulu nettoyer vos méta-descriptions ? Si vous souhaitez extraire du contenu dans Excel, puis découper le contenu à utiliser dans un champ Meta Description (150 à 160 caractères), vous pouvez le faire en utilisant cette formule à partir de My Spot. Il casse proprement la description à un espace puis ajoute le …:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN( GAUCHE(A1,154))-LEN(REMPLACER(GAUCHE(A1,154)," ",""))))) & SI(LEN(A1)>TROUVE("*",SUBSTITUE(A1" ", "*",LEN(GAUCHE(A1,154))-LEN(SUBSTITUT(GAUCHE(A1,154)," ","")))),"…",""),A1)

Bien sûr , celles-ci ne sont pas censées être exhaustives… juste quelques formules rapides pour vous aider à démarrer ! Quelles autres formules utilisez-vous ? Ajoutez-les dans les commentaires et je vous remercierai au fur et à mesure que je mettrai à jour cet article.