Vous collez une formule dans Excel, appuyez sur Entrée, et obtenez une erreur #VALEUR! flagrante au lieu du texte souhaité. Neuf fois sur dix, le coupable est un délimiteur manquant. La fonction a cherché un caractère absent, et toute la formule s'est effondrée. Voici comment corriger cela proprement.
Pourquoi l'erreur se produit
Les fonctions de texte d'Excel comme TEXTBEFORE et TEXTAFTER fonctionnent en recherchant un délimiteur spécifique dans une chaîne. Si ce délimiteur n'existe pas dans la cellule, elles renvoient une erreur #VALEUR! par défaut. C'est un comportement tout à fait normal, pas un bug.
Le problème, c'est que les données réelles sont désordonnées. Toutes les lignes de votre feuille de calcul ne suivent pas le même format, et une seule virgule ou un seul tiret manquant peut casser une formule appliquée à des centaines de lignes.
La solution rapide : SIERREUR
Le moyen le plus rapide de gérer cela est d'envelopper votre formule dans SIERREUR. Cette fonction intercepte toute erreur renvoyée par une formule et la remplace par une valeur de votre choix.
La syntaxe de base ressemble à ceci :
Astuce : Utilisez =SIERREUR(TEXTBEFORE(A1, "-"), A1) pour renvoyer la valeur originale de la cellule lorsqu'aucun délimiteur n'est trouvé. Ainsi, vos données restent intactes au lieu d'afficher une erreur.
Si vous préférez renvoyer une chaîne vide, utilisez simplement =SIERREUR(TEXTBEFORE(A1, "-"), ""). Cela garde votre feuille de calcul propre si vous n'avez pas besoin d'une valeur de remplacement.
Scénarios d'erreur de TEXTBEFORE
La fonction TEXTBEFORE est puissante mais stricte. Elle génère une erreur #VALEUR! dans quelques situations spécifiques qu'il est bon de connaître.
- La chaîne de délimiteur est introuvable dans la cellule.
- Vous spécifiez un numéro d'instance supérieur au nombre d'occurrences du délimiteur.
- La cellule est vide et vous recherchez un délimiteur non vide.
- Vous passez une plage au lieu d'une seule cellule (dans les anciennes versions d'Excel).
SIERREUR gère tous ces cas en une seule fois, c'est pourquoi c'est la correction de formule privilégiée par la plupart des utilisateurs.
Comparaison du comportement de TEXTBEFORE et SIERREUR
| Formule | Délimiteur trouvé | Délimiteur manquant |
|---|---|---|
| TEXTBEFORE seul | Renvoie le texte avant le délimiteur | Erreur #VALEUR! |
| SIERREUR + TEXTBEFORE | Renvoie le texte avant le délimiteur | Renvoie votre valeur de remplacement |
| TEXTBEFORE avec l'argument if_not_found | Renvoie le texte avant le délimiteur | Renvoie la valeur de remplacement spécifiée (Excel 365) |
L'argument de remplacement intégré (Excel 365)
Si vous utilisez Microsoft 365, TEXTBEFORE dispose en fait d'un argument intégré pour gérer les délimiteurs manquants. Le quatrième argument, if_not_found, vous permet de vous passer entièrement de SIERREUR.
Par exemple : =TEXTBEFORE(A1, "-", 1, 0, 1, "aucun") renvoie "aucun" lorsque le tiret est introuvable. C'est une approche plus propre si vous pouvez compter sur la dernière version d'Excel.
Attention : L'argument if_not_found de TEXTBEFORE est uniquement disponible dans Excel 365 et Excel 2024. Si vous partagez votre fichier avec quelqu'un utilisant une version plus ancienne, cette personne verra toujours l'erreur. SIERREUR est plus sûr pour les classeurs partagés.
Vérifier les données avant qu'elles n'arrivent dans Excel
Parfois, la meilleure solution est de corriger vos données avant même qu'elles n'atteignent vos formules. Si vous travaillez avec des fichiers exportés dont les délimiteurs sont incohérents, un nettoyage rapide vous évitera bien des problèmes en aval.
Vous pouvez utiliser un convertisseur de délimiteurs en ligne pour standardiser les délimiteurs de votre fichier avant l'importation. Ainsi, chaque ligne utilise le même séparateur et vos formules n'ont pas à compenser les incohérences.
Erreurs courantes à éviter
- Utiliser SIERREUR pour masquer de vraies erreurs que vous devriez corriger dans vos données.
- Oublier que TEXTBEFORE est sensible à la casse par défaut.
- Imbriquer trop de fonctions SIERREUR au lieu de nettoyer les données sources.
- Supposer que toutes les lignes ont la même structure alors que ce n'est pas le cas.
Points clés
- TEXTBEFORE et les fonctions similaires renvoient #VALEUR! lorsque le délimiteur est introuvable dans la cellule.
- Envelopper votre formule avec SIERREUR est la correction la plus rapide et la plus compatible.
- Les utilisateurs d'Excel 365 peuvent utiliser l'argument intégré
if_not_foundde TEXTBEFORE pour des formules plus propres. - Nettoyer les délimiteurs incohérents avant l'importation réduit le besoin de gestion d'erreurs dès le départ.
- SIERREUR est préférable pour les classeurs partagés car il fonctionne sur toutes les versions modernes d'Excel.
Corrigez les données, pas seulement la formule
La gestion des erreurs dans les formules est utile, mais ce n'est qu'un pansement. Si vos données manquent systématiquement des délimiteurs attendus par vos formules, c'est le signe que les données elles-mêmes nécessitent votre attention. Un peu de préparation en amont, que ce soit en utilisant un convertisseur de délimiteurs ou en nettoyant votre CSV avant l'importation, signifie moins de contournements dans les formules par la suite.
Une fois vos données cohérentes, vos formules restent simples et vos feuilles de calcul restent rapides. C'est un bien meilleur résultat qu'une chaîne SIERREUR profondément imbriquée que personne ne voudra déboguer six mois plus tard.