Formules de nettoyage de données courantes dans Excel

excellentes formules

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 à rechercher plus tard! Aujourd'hui, nous avons eu un client qui nous a remis un fichier de données client qui a été 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 aider. J'ai pensé partager certains d'entre eux ici juste pour que d'autres puissent les utiliser.

Supprimer les caractères non numériques

Les systèmes exigent souvent que les numéros de téléphone soient insérés dans une formule spécifique à 11 chiffres avec le code du pays et sans ponctuation. Cependant, les gens entrent souvent ces données avec des tirets et des points à la place. Voici une excellente formule pour suppression de 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 se rendent pas compte que vous n'avez pas toujours à écrire des formules hiérarchiques complexes et que vous pouvez écrire une instruction OR à la place. Dans cet exemple ci-dessous, je veux vérifier A2, B2, C2, D2 ou E2 pour les données manquantes. Si des données sont manquantes, je vais renvoyer un 0, sinon un 1. Cela me permettra de trier les données et de supprimer les enregistrements incomplets.

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

Ajuster et concaténer les champs

Si vos données ont des champs Prénom et Nom, mais que votre importation a un champ Nom complet, vous pouvez concaténer les champs ensemble proprement à l'aide de la fonction de concaténation Excel 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 ne contient pas de données:

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

Vérifier une adresse e-mail valide

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

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

Extraire les noms et prénoms

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

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

Et le nom:

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

Limitez le nombre de caractères et ajoutez…

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

=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)

Bien sûr, ceux-ci ne sont pas censés être complets… juste quelques formules rapides pour vous aider à prendre un bon départ! Quelles autres formules utilisez-vous? Ajoutez-les dans les commentaires et je vous en féliciterai en mettant à jour cet article.

Que pensez-vous?

Ce site utilise Akismet pour réduire les spams. Découvrez comment sont traitées les données de vos commentaires..