Formules Excel pour le nettoyage commun des données

Pendant des années, j’ai utilisé la publication comme ressource pour décrire comment faire les choses et en garder une trace pour pouvoir la consulter plus tard ! Un client nous a remis un fichier de données client qui s’est avéré 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 permettant d’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 nous aider. J’ai pensé en partager quelques-uns ici afin que vous puissiez les utiliser.

Supprimer les caractères non numériques

Les systèmes nécessitent souvent que les numéros de téléphone soient insérés dans une formule spécifique à 11 chiffres avec l’indicatif du pays et sans ponctuation. Cependant, les gens saisissent souvent ces données avec des tirets et des points. 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 copier la colonne résultante et utiliser Édition > Coller les valeurs pour écrire sur les données avec le résultat correctement formaté.

Évaluer plusieurs champs avec un OU

Nous supprimons souvent les enregistrements incomplets d’une importation. Les utilisateurs ne réalisent pas qu’il n’est pas toujours nécessaire d’écrire des formules hiérarchiques complexes et que vous pouvez écrire une instruction OU à la place. Je souhaite vérifier A2, B2, C2, D2 ou E2 pour les données manquantes dans l’exemple ci-dessous. Si des données sont manquantes, je renverrai 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)

Découper et concaténer des champs

Si vos données comportent des champs Prénom et Nom, mais que votre importation comporte un champ Nom complet, vous pouvez concaténer les champs ensemble à l’aide de la fonction Excel intégrée Concaténer, mais assurez-vous d’utiliser TRIM pour supprimer tous les espaces vides avant ou après le champ Nom et Prénom. texte. Nous enveloppons tout le champ avec TRIM si l’un des champs ne contient pas de données :

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

Vérifiez l’adresse e-mail valide

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

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

Extraire les noms et prénoms

Parfois, le problème est inverse. Vos données comportent 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 récupèrent le texte si nécessaire. Il gère également s’il n’y a pas de nom de famille ou 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 de famille :

=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à eu envie de nettoyer vos méta descriptions ? Si vous souhaitez extraire du contenu dans Excel, puis le découper pour l’utiliser dans un champ Meta Description (150 à 160 caractères), vous pouvez le faire en utilisant cette formule. Il coupe proprement la description dans 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, 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 donnerai du crédit au fur et à mesure que je mettrai à jour cet article.