Formules de nettoyage de données courantes dans Excel »Zone Martech

Pendant des années, j’ai utilisé la publication comme ressource non seulement pour décrire comment faire les choses, mais aussi pour garder une trace de moi-même à consulter plus tard! Aujourd'hui, nous avons eu un client qui nous a remis un fichier de données client qui a été une catastrophe. Presque tous les champs ont été 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. Je pensais partager certaines de ces informations ici pour que d'autres puissent les utiliser.

Supprimer les caractères non numériques

Les systèmes nécessitent souvent l'insertion de numéros de téléphone dans une formule spécifique à 11 chiffres avec le code du pays et non ponctuation. Cependant, les gens saisissent 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 é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. S'il manque des données, je vais retourner 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 les champs

Si vos données ont un champ Prénom et Nom, mais votre importation a un champ de nom complet, vous pouvez concaténer correctement les champs à l'aide de la fonction de concaténation de fonction Excel intégrée, mais assurez-vous d'utiliser TRIM pour supprimer tous les espaces vides avant ou après le texte. Nous encapsulons 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 la validité de l'e-mail Adresse

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

 = AND (FIND ("@", A2), FIND (".", A2), ISERROR (FIND ("", A2))) 

Extraire le prénom et le nom [19659003] Parfois, le problème est le contraire. Vos données ont un champ de nom complet, mais vous devez analyser les prénom et 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 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à voulu nettoyer vos méta descriptions? Si vous souhaitez extraire du contenu dans Excel, puis rogner le contenu pour l'utiliser dans un champ de méta-description (150 à 160 caractères), vous pouvez le faire en utilisant cette formule de My Spot . Il casse proprement la description dans un espace, puis ajoute le …:

 = IF (LEN (A1)> 155, LEFT (A1, FIND ("*", SUBSTITUTE (A1, "", "*", LEN ( GAUCHE (A1,154)) - LEN (SUBSTITUTE (GAUCHE (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 exhaustifs… juste quelques formules rapides pour vous aider à démarrer rapidement! Quelles autres formules utilisez-vous? Ajoutez-les dans les commentaires et je vous rendrai hommage en mettant à jour cet article.

Téléchargez un livre blanc sur le marketing sponsorisé:

Microsoft Teams – Carte de référence gratuite

Cette référence Microsoft Teams fournit des raccourcis, des conseils et des astuces pour ce logiciel inter-bureaux populaire. Télécharger maintenant