Fonctions texte : extraire et nettoyer numéros de compte et références

Les données qui arrivent dans Excel ne sont presque jamais dans la forme où on les utilise. Un compte sort sur sept chiffres alors qu’on raisonne sur quatre, un tiers arrive collé à son identifiant fiscal, une référence mêle lettres et chiffres. Les fonctions texte permettent de découper, nettoyer et recomposer ces chaînes de caractères : c’est la boîte à outils de tout import comptable.

Ce que vous saurez faire à la fin de l’épisode

• extraire le début, la fin ou le milieu d’un texte ;
• repérer la position d’un séparateur et découper autour de lui ;
• mesurer une longueur pour contrôler un identifiant ;
• nettoyer espaces et caractères parasites ;
• convertir un extrait en nombre quand il le faut.

Les six fonctions de base

Trois précisions : la première position d’un texte est 1 (pas 0) ; TROUVE renvoie #VALEUR! si le fragment est absent (on l’encadre souvent de SIERREUR) ; et TROUVE distingue majuscules et minuscules alors que CHERCHE les ignore et accepte les jokers.

Le cas : un export de comptes auxiliaires

Le logiciel d’Atlas Négoce exporte les tiers avec un compte sur sept chiffres (les quatre premiers sont le compte général du CGNC, les trois derniers la subdivision du tiers) et une colonne « Tiers - ICE » qui accole le nom et l’identifiant commun de l’entreprise (15 chiffres). On veut : la classe, le compte général, la subdivision, le nom et l’ICE, et un contrôle de l’ICE.

Figure 1 : cinq tiers. Repère 1 : décomposition du compte ; repère 2 : extraction de l’ICE ; repère 3 : contrôle de longueur.

La classe : dans le CGNC, le premier chiffre du compte indique la classe (1 financement permanent, 2 actif immobilisé, 3 actif circulant, 4 passif circulant, 5 trésorerie, 6 charges, 7 produits, 8 résultats). La formule : =GAUCHE(SUPPRESPACE(A2);1). Les cinq tiers donnent 3, 4, 5, 6 et 7.

Le compte général (repère 1) : =GAUCHE(SUPPRESPACE(A2);4) donne 3421, 4411, 5141, 6111 et 7111. La subdivision : =DROITE(SUPPRESPACE(A2);3) donne 000, 200, 000, 100, 000.

SUPPRESPACE est là parce que la dernière ligne contient un compte avec des espaces autour (7111000). Sans nettoyage, GAUCHE(A6;1) aurait renvoyé un espace, et la « classe » aurait été fausse sans erreur visible.

Le nom et l’ICE. La colonne B contient Maroc Équipements - 001234567000089. Le séparateur est le groupe - (espace, tiret, espace). Pour le nom, on prend tout ce qui précède :

=GAUCHE(B2; TROUVE(" - ";B2) - 1)

TROUVE(" - ";B2) renvoie la position du séparateur (ici 18) ; on retire 1 pour s’arrêter avant. Résultat : « Maroc Équipements ». Pour l’ICE (repère 2), on commence 3 caractères après le début du séparateur et l’on prend 15 caractères :

=STXT(B2; TROUVE(" - ";B2) + 3; 15)

Résultat : 001234567000089. La logique « position du séparateur ± décalage » est la clé de toutes les extractions : on mesure d’abord où l’on se trouve, puis on découpe.

Le contrôle (repère 3) : =SI(NBCAR(G2)=15;"OK";"ICE à vérifier"). Pour les quatre premiers tiers, 15 caractères, OK. Pour le cinquième, l’ICE extrait compte seulement 14 caractères (00567890100007) : la ligne est signalée « ICE à vérifier », probablement un chiffre manquant à la saisie. Le comptable n’a pas à deviner : il demande l’identifiant exact au tiers ou le contrôle sur la fiche officielle de l’entreprise.

Les extractions renvoient du texte

Un piège que vous reconnaîtrez : =GAUCHE(A2;1) renvoie "3" (texte), pas le nombre 3. Alignée à gauche, elle ne s’additionne pas et ne se compare pas à un nombre (=B2=3 renvoie FAUX). Pour obtenir un vrai nombre :

=CNUM(GAUCHE(SUPPRESPACE(A2);1))

Même problème dans l’autre sens : une recherche du compte 3421 (nombre) dans une colonne de comptes en texte échoue. Décidez une fois pour toutes si vos numéros de compte sont du texte ou des nombres, et appliquez-le partout. Pour les comptes, le texte est préférable (zéros conservés, comptes de longueur variable).

Nettoyer : le trio SUPPRESPACE, SUBSTITUE, EPURAGE

Le nettoyage complet d’une cellule d’import s’écrit souvent en cascade :

=SUPPRESPACE(EPURAGE(SUBSTITUE(A2;CAR(160);" ")))

Appliquez-le dans une colonne à côté de la source, puis collez-le en valeurs (épisode 6) sur la colonne d’origine une fois contrôlé.

Un autre usage : harmoniser les noms de tiers avant un rapprochement. MAJUSCULE(SUPPRESPACE(B2)) rend « Sud Distribution », « SUD DISTRIBUTION » et « sud distribution » comparables.

Sans formule : Texte en colonnes et Remplissage instantané

Deux outils font parfois la même chose plus vite :

• Données › Convertir (texte en colonnes) : découpe selon un séparateur (point-virgule, tiret) ou à largeur fixe. Pratique pour un travail ponctuel (épisode 18).
• Remplissage instantané (Ctrl + E) : saisissez à côté de la première ligne le résultat voulu (« Maroc Équipements »), recommencez sur la deuxième, appuyez sur Ctrl + E : Excel devine le motif et remplit le reste. Rapide mais non traçable : il n’y a pas de formule, donc si les données changent, rien ne se met à jour, et sur des motifs irréguliers il se trompe sans le dire. Contrôlez toujours le résultat.

Pour un fichier qui sera réalimenté, les formules (ou Power Query, épisode 46) restent la bonne réponse.

À vous de jouer

Dans l’onglet Comptes du classeur d’exercice :

1. Complétez les colonnes C à H avec les formules ci-dessus (la colonne D donne le compte général, G l’ICE).
2. Constatez la ligne 6 : que fait SUPPRESPACE ? Que renvoie le contrôle ?
3. Ajoutez une colonne I : =CNUM(C2) pour la classe en nombre. Calculez ensuite =SOMME(I2:I6) : que trouvez-vous ?
4. Écrivez une formule de clé « compte-tiers » : =D2&"-"&F2 (épisode 16) et observez le résultat.

Correction. La classe donne 3, 4, 5, 6, 7. À la ligne 6, le compte 7111000 est correctement décomposé (classe 7, compte 7111, subdivision 000) grâce à SUPPRESPACE, et le contrôle renvoie « ICE à vérifier » car l’ICE ne fait que 14 caractères. La somme des classes en nombre vaut 25 (3 + 4 + 5 + 6 + 7). La clé de la première ligne est 3421-Maroc Équipements.

À retenir

• GAUCHE, DROITE, STXT extraient ; TROUVE localise ; NBCAR mesure ; SUPPRESPACE nettoie.
• Pour découper autour d’un séparateur : TROUVE donne la position, la formule ajuste le décalage.
• Les extraits sont du texte : CNUM pour calculer.
• SUPPRESPACE ne retire pas l’espace insécable : SUBSTITUE(…;CAR(160);" ").
• Un contrôle de longueur (NBCAR) détecte les identifiants incomplets, sans deviner.

Épisode 16 : l’opération inverse, assembler du texte pour fabriquer des libellés d’écriture et des numéros de pièce automatiques.