Excel : nettoyer des données exportées d’un logiciel (espaces, montants en texte, dates, doublons)

Votre logiciel de paie ou de facturation exporte un fichier Excel. Vous y appliquez une somme : résultat zéro. Une recherche : #N/A. Les causes sont presque toujours les mêmes : des espaces invisibles, des montants stockés comme du texte, des dates non reconnues, des doublons. Ce tutoriel donne la méthode et les formules pour nettoyer un export en quelques minutes.

Règle d’or : ne jamais toucher aux données brutes

Gardez la feuille d’import intacte. Créez à droite des colonnes de nettoyage, vérifiez-les, puis collez les valeurs. En cas d’erreur, vous pouvez recommencer.

Les quatre défauts les plus fréquents

Nettoyer les espaces

SUPPRESPACE supprime les espaces au début et à la fin, et réduit les espaces multiples à un seul :

=SUPPRESPACE(B2)

Si le texte contient un espace insécable (fréquent dans les exports web) :

=SUPPRESPACE(SUBSTITUE(B2;CAR(160);" "))

Pour normaliser la casse : =NOMPROPRE(SUPPRESPACE(B2)) met une majuscule à chaque mot (« Virement CLIENT alpha » devient « Virement Client Alpha »). MAJUSCULE et MINUSCULE existent aussi.

Convertir un montant texte en nombre

Un montant importé comme « 12 450,00 » (avec espace de milliers) est du texte. Il faut supprimer les espaces et, selon les réglages régionaux, remplacer la virgule :

=CNUM(SUBSTITUE(SUBSTITUE(C2;" ";"");",";"."))

Figure 1 : à gauche les données brutes (repère 1), à droite le montant converti (repère 2) et le libellé nettoyé (repère 3).

Selon que votre Excel utilise la virgule ou le point comme séparateur décimal, adaptez : en configuration française, =CNUM(SUBSTITUE(C2;" ";"")) suffit si la virgule est déjà le séparateur décimal.

Alternative rapide sans formule : sélectionnez la colonne > Données > Convertir > Terminer. Excel retraite le contenu et le reconnaît comme nombre (selon la configuration régionale).

Convertir une date texte

Pour « 2026-03-05 » (format ISO) :

=DATE(GAUCHE(A2;4);STXT(A2;6;2);DROITE(A2;2))

GAUCHE prend l’année, STXT extrait le mois (à partir du 6e caractère), DROITE le jour. Appliquez ensuite le format de date voulu. Pour « 05/03/2026 » reçue comme texte avec un séparateur différent, la même logique s’applique en extrayant les trois parties. DATEVAL convertit directement un texte reconnaissable par la configuration régionale.

Extraire une partie d’un texte

Un libellé du type « 6111 - Achats de marchandises » :

Les formes de montants à traiter

Les exports ne se ressemblent pas. Voici les cas les plus fréquents et la formule qui convient (colonne source en C2) :

Pour combiner plusieurs cas dans un même fichier, enchaînez les SUBSTITUE : =CNUM(SUBSTITUE(SUBSTITUE(SUBSTITUE(C2;" ";"");CAR(160);"");UNICAR(8239);"")).

Un exercice complet sur cinq lignes

Un export de grand livre fournisseurs, avant nettoyage :

Après nettoyage (colonnes à droite, puis collées en valeurs) :

Les formules utilisées : =SUPPRESPACE(A2) pour le compte, =NOMPROPRE(SUPPRESPACE(B2)) pour le libellé, la formule des parenthèses pour le négatif, et =DATE(GAUCHE(D2;4);STXT(D2;6;2);DROITE(D2;2)) pour la date. Les deux lignes « Gamma » sont signalées par =NB.SI.ENS($B$2:$B$6;B2;$C$2:$C$6;C2;$D$2:$D$6;D2)>1 : on vérifie avant de supprimer, car deux factures légitimes peuvent avoir le même montant le même jour.

Contrôle : somme des montants nettoyés = 12 450 − 3 200 + 8 000 + 8 000 + 5 500 = 30 750 ; le logiciel source doit afficher la même somme (avec le négatif).

Power Query pas à pas

Pour un import mensuel, la méthode recommandée est Power Query. Ses étapes se lisent dans l’éditeur avancé ; voici l’équivalent du nettoyage ci-dessus (adaptez le chemin et les noms de colonnes) :

let
    Source = Excel.Workbook(File.Contents("C:\Imports\grand-livre.xlsx"), null, true),
    Feuille = Source{[Item = "Fournisseurs", Kind = "Sheet"]}[Data],
    Entetes = Table.PromoteHeaders(Feuille, [PromoteAllScalars = true]),
    Libelles = Table.TransformColumns(Entetes, {{"Libellé", Text.Trim, type text}, {"Compte", Text.Trim, type text}}),
    Types = Table.TransformColumnTypes(Libelles, {{"Montant", type number}, {"Date", type date}}, "fr-FR"),
    SansDoublons = Table.Distinct(Types, {"Libellé", "Montant", "Date"})
in
    SansDoublons

Dans l’interface, les mêmes opérations se font par clics : Données > Obtenir des données > À partir d’un fichier, puis Transformer : promouvoir la première ligne en en-têtes, Format > Supprimer les espaces, changer le type des colonnes en précisant la région (France), et Supprimer les doublons sur les colonnes choisies. Chaque mois, il suffit de remplacer le fichier source et de cliquer sur Actualiser tout.

Attention : Table.Distinct supprime ici les doublons sans les montrer. Pour les repérer d’abord, ajoutez une colonne de comptage (Grouper par) ou chargez le résultat et la liste des doublons dans deux feuilles séparées.

Supprimer les doublons

Sélectionnez la plage > Données > Supprimer les doublons > cochez les colonnes qui définissent un doublon (par exemple N° de facture + Montant). Attention : l’action est définitive ; travaillez sur une copie. Pour repérer sans supprimer, utilisez la mise en forme conditionnelle > Valeurs en double, ou =NB.SI($A$2:$A$500;A2)>1.

Gérer les erreurs

Aller vers l’automatisation : Power Query

Pour les imports mensuels (balance, relevé bancaire), Power Query (onglet Données > Obtenir des données) enregistre chaque étape de nettoyage : suppression d’espaces, conversion de types, filtre, renommage. À l’import suivant, un clic sur Actualiser rejoue tout. Aucune formule à recopier, et des milliers de lignes se traitent sans ralentir le fichier.

Dix cas de données sales et la formule qui les corrige

Voici un catalogue de défauts rencontrés dans les exports de logiciels, de banques et de fichiers clients, avec la formule (en cellule B2, donnée brute en A2) qui les traite.

Appliquez chaque formule dans une colonne à côté de la donnée brute, puis copiez-collez les valeurs dans la feuille de travail. La donnée brute reste intacte, ce qui permet de refaire ou de justifier le nettoyage.

Fusionner plusieurs fichiers avec Power Query

Un dossier contient douze relevés mensuels (CSV) de même structure. Power Query les combine automatiquement :

1. Données > Obtenir des données > À partir d’un fichier > À partir d’un dossier, choisir le dossier.
2. Combiner > Combiner et transformer : Power Query lit le premier fichier comme modèle et applique les mêmes étapes aux autres.
3. Dans l’éditeur : Utiliser la première ligne comme en-têtes, définir le type de chaque colonne (date, texte, nombre décimal avec les paramètres régionaux français), supprimer les lignes vides et les totaux intermédiaires.
4. Fractionner une colonne composée (par un délimiteur) et Supprimer les doublons.
5. Fermer et charger dans une table Excel.

À chaque nouveau relevé déposé dans le dossier, Données > Actualiser tout met la table à jour. Un fichier traité manuellement en trois heures chaque mois l’est en trois minutes.

Contrôles de cohérence après nettoyage

Ces contrôles prennent une minute et évitent d’importer en comptabilité un fichier qui introduirait un écart.

Préparer un fichier d’import comptable

Beaucoup de logiciels acceptent l’import d’écritures depuis un fichier CSV ou Excel. Le fichier doit respecter un format strict :

La contrainte la plus importante : chaque écriture est équilibrée (total débit = total crédit). Une colonne de contrôle =SOMME.SI(Pièce;[@Pièce];Débit)-SOMME.SI(Pièce;[@Pièce];Crédit) vérifie l’équilibre par pièce. Avant l’import définitif, faites toujours un essai sur une copie de la base ou un exercice test.

Contrôles après nettoyage

1. Le nombre de lignes avant/après est cohérent (hors doublons supprimés).
2. La somme des montants nettoyés égale celle du logiciel source.
3. Aucune cellule d’erreur (NB.SI.ENS sur ESTERREUR).
4. Les dates sont triables et couvrent la bonne période.
5. Les clés de recherche trouvent bien leurs correspondances.

Fiche pratique : les dix fonctions du nettoyage

Cinq réflexes :

• Ne jamais modifier la donnée brute : travailler dans des colonnes voisines.
• Contrôler les totaux avant et après nettoyage.
• Automatiser les imports récurrents avec Power Query.
• Documenter chaque étape dans un onglet de notes.
• Tester sur une copie avant d’importer en comptabilité.

Cas particuliers et situations limites

Le fichier contient des caractères spéciaux (accents mal encodés). L’import CSV doit se faire en précisant l’encodage (UTF-8 ou ANSI) ; Power Query propose le choix de l’origine du fichier.

Les montants contiennent des symboles de devise (« MAD », « DH »). Retirer le symbole avec SUBSTITUE avant CNUM ; utiliser une colonne de devise séparée si plusieurs devises coexistent.

Les libellés sont tronqués par le logiciel d’export. Récupérer le champ complet à la source plutôt que de tenter de reconstituer ; un libellé tronqué dans un justificatif est un risque de mauvaise imputation.

Les lignes de sous-totaux sont mélangées aux données. Les filtrer avec une colonne de test (=NON(ESTTEXTE(...))) ou dans Power Query avant tout calcul.

Plusieurs personnes nettoient le même fichier. Centraliser les règles dans une requête Power Query unique et partagée pour garantir un résultat identique.

En résumé

Conserver la donnée brute, nettoyer dans des colonnes voisines, contrôler les totaux avant et après, automatiser avec Power Query les imports récurrents, documenter les étapes et tester sur une copie avant tout import en comptabilité.