Comment ajouter des zéros devant vos numéros Excel (la 2ème méthode change tout) ?

Dans ce tutoriel, je vais vous montrer comment résoudre définitivement le problème des zéros qui disparaissent devant vos numéros dans Excel.

Que ce soit pour des numéros de factures, des codes employés ou des références produits, Excel a cette fâcheuse tendance à supprimer automatiquement les zéros non significatifs.

Nous allons découvrir ensemble deux méthodes pour contrer ce comportement : la première est rapide mais présente des limitations importantes, tandis que la seconde est la solution professionnelle qui garantit un résultat fiable dans toutes les situations.

Restez jusqu'à la fin car je vous révèle aussi comment utiliser ces valeurs formatées dans vos calculs, un problème que personne n'explique jamais et qui peut bloquer complètement votre travail.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons un inventaire d'entreprise avec des numéros de factures et des codes employés.

Le problème ici c’est que nos numéros de factures devraient commencer par trois zéros pour respecter le format standard de l'entreprise (000123, 000456, etc.), et nos codes employés devraient commencer par deux zéros suivis de la lettre E (00E45, 00E12, etc.).

Nous avons beau saisir ces zéros, dès que nous validons en appuyant sur la touche [Entrée], Excel interprète automatiquement les données qui ressemblent à des nombres comme des valeurs numériques.

Pour Excel, il n'y a aucune différence entre 00123 et 123, car mathématiquement ces deux valeurs sont identiques.

C'est un comportement logique pour un tableur conçu pour faire des calculs, mais cela pose un problème majeur pour les références, les identifiants et tous les codes qui nécessitent un format précis.

Ce comportement a plusieurs conséquences dans notre travail quotidien :

• Premièrement, nous perdons la cohérence visuelle de notre numérotation, ce qui rend les documents moins professionnels.
• Deuxièmement, et c'est probablement le plus important, nous ne sommes plus conformes aux standards de l'entreprise, ce qui peut poser des problèmes lors d'exports vers d'autres logiciels de gestion ou de comptabilité.

2. Méthode 1 : Le formatage personnalisé

   2.1 Application sur les numéros de factures

Commençons par la méthode la plus rapide, qui consiste à modifier l'apparence visuelle de nos cellules sans toucher aux valeurs elles-mêmes.

Nous sélectionnons d'abord toute la plage de nos numéros de factures, puis nous faisons un clic droit n'importe où sur la sélection.

Un menu contextuel apparaît avec plusieurs options, nous cliquons sur « Format de cellule », qui se trouve généralement vers le milieu de ce menu.

Une boîte de dialogue s'ouvre alors, affichant plusieurs onglets en haut.

Dans l'onglet « Nombre » , nous nous plaçons dans la liste de gauche, dans laquelle nous retrouvons plusieurs catégories comme Standard, Nombre, Monétaire, etc.

Nous descendons tout en bas de cette liste jusqu'à trouver « Personnalisé ».

C'est cette catégorie qui va nous permettre de définir exactement comment nous voulons que nos nombres s'affichent.

Dans la zone de texte « Type », nous supprimons ce qui s'y trouve afin d’y définir notre format personnalisé.

Pour obtenir des numéros à six chiffres (ce qui nous donnera nos trois zéros devant), nous tapons six fois le chiffre zéro :

000000

Chaque zéro représente un espace réservé pour un chiffre, et Excel remplira automatiquement les positions vides avec des zéros.

Nous validons en cliquant sur le bouton « OK » en bas de la boîte de dialogue.

Immédiatement, nous voyons nos numéros de factures se transformer : 123 devient 000123, 456 devient 000456, et ainsi de suite.

Visuellement, le résultat est exactement ce que nous recherchions.

   2.2 Le piège de cette méthode

Maintenant, testons la robustesse de cette solution. Dans une cellule vide, nous tapons une formule simple :

=B2

Cette formule va simplement afficher le contenu de la cellule B2.

Nous appuyons sur [Entrée] et nous voyons s'afficher 123, et non pas 000123.

Cela révèle la limitation majeure de cette méthode : les zéros ne sont qu'un habillage visuel, un déguisement appliqué à la cellule.

La valeur réelle stockée dans B2 reste 123.

Pour le vérifier encore plus clairement, nous cliquons sur la cellule B2 et nous regardons dans la barre de formule en haut de l'écran. Là aussi, nous voyons 123, pas 000123.

Cette distinction entre ce qui est affiché et ce qui est réellement stocké va poser de nombreux problèmes dans des situations pratiques.

• Si nous copions la cellule B2 avec [Ctrl]+[C], puis que nous la collons ailleurs avec un collage spécial en valeur ([Ctrl]+[Alt]+[V], puis nous choisissons « Valeurs »), les zéros disparaissent immédiatement.
• Si nous exportons notre tableau vers un fichier CSV pour l'importer dans un autre logiciel, les zéros ne seront pas présents dans le fichier exporté.
• Si nous envoyons ce fichier à un collègue qui n'a pas exactement le même format de cellule, il verra 123 au lieu de 000123.

Cette méthode peut donc être utilisée uniquement pour des besoins d'affichage temporaire et local, mais elle n'est absolument pas recommandée pour un usage professionnel où les données doivent être partagées, exportées ou manipulées.

3. Méthode 2 : La fonction TEXTE (solution professionnelle)

   3.1 Principe et application

Passons maintenant à la solution qui va vraiment modifier la valeur de nos cellules, et non plus simplement leur apparence.

Cette solution utilise la fonction TEXTE, qui est une fonction de conversion extrêmement puissante.

Elle prend un nombre et le transforme en texte selon un format que nous spécifions.

Nous commençons par créer une colonne auxiliaire.

Dans la cellule E2, nous tapons la formule suivante :

=TEXTE(B2;"000000")

Où :

• Le premier argument, B2, est notre numéro de facture source qui vaut actuellement 123.
• Le deuxième argument, entre guillemets, est le format que nous voulons appliquer. Comme nous l’avons vu avec la première méthode, les six zéros signifient que nous voulons un texte de six chiffres, et Excel ajoutera automatiquement des zéros devant si nécessaire.

Nous validons avec [Entrée] et nous voyons apparaître 000123 dans la cellule E2.

Maintenant, vérifions que cette fois-ci, les zéros font vraiment partie de la valeur.

Nous cliquons sur E2 et nous regardons la barre de formule.

Nous voyons bien notre formule, mais si nous voulons voir le résultat réel, nous devons le copier en valeur.

Nous copions donc la cellule E2 avec [Ctrl]+[C], puis nous la collons dans une cellule vide, par exemple F2, en utilisant le collage spécial valeurs.

Pour cela, nous utilisons le raccourci [Ctrl]+[Alt]+[V], ce qui ouvre la boîte de dialogue de collage spécial, puis nous cliquons sur « Valeurs » et nous validons.

Maintenant, en cliquant sur F2, nous voyons dans la barre de formule : 000123. Cette fois-ci, les zéros font vraiment partie de la valeur stockée dans la cellule.

Maintenant que nous avons validé que notre formule fonctionne correctement, nous allons l'appliquer à toutes nos factures.

Nous retournons dans la cellule E2 qui contient notre formule et nous double-cliquons sur la poignée de recopie pour qu’Excel étende automatiquement la formule sur toute la colonne.

Puis, nous allons remplacer nos anciennes valeurs par ces nouvelles valeurs, et les sélectionnant, puis en les copiant avec [Ctrl]+[C].

Maintenant, attention, nous ne faisons pas un simple [Ctrl]+[V], mais un collage spécial valeurs avec [Ctrl]+[Alt]+[V], puis « Valeurs ».

Nos numéros de factures dans la colonne B sont maintenant définitivement formatés avec les zéros, et nous pouvons supprimer la colonne auxiliaire E en cliquant sur l'en-tête de colonne E, puis en faisant un clic droit et en choisissant « Supprimer ».

   3.2 Conserver les zéros lors de la saisie

Attention, la méthode que nous venons de voir ici, nous permet de convertir des valeurs numériques déjà saisie en ajoutant les zéros.

Si nous devons ajouter manuellement de nouvelles lignes, à la suite du tableau, les zéros seront de nouveau supprimés automatiquement.

Pour éviter ce comportement, nous devons demander à Excel de traiter ces valeurs non pas comme des valeurs numériques, mais directement comme du texte, en utilisant une apostrophe simple devant le nombre.

Ainsi, au lieu de saisir « 000123 », nous saisissons « '000123 ».

   3.3 Utiliser ces valeurs dans des calculs

Maintenant que nous avons transformé nos numéros en texte, nous devons être conscients d'une limitation importante : Excel ne peut plus les utiliser directement dans des calculs numériques.

Si nous essayons de saisir la formule :

=SOMME(B2:B6)

Le résultat sera zéro, car Excel ne peut pas additionner du texte.

Pour éviter ce problème, il est possible de maintenir deux colonnes : une colonne cachée avec les valeurs numériques originales pour les calculs, et une colonne visible avec les valeurs formatées en texte pour l'affichage et l'export.

Mais cette technique est un peu …, et elle posera des problèmes si nous souhaitons exporter nos données.

Si nous souhaitons utiliser nos valeurs directement dans une formule, nous pouvons utiliser la fonction CNUM.

Sur Excel 365 et Excel 2021, nous pouvons directement écrire

=SOMME(CNUM(B2:B6))

Par contre, sur les versions antérieures d'Excel, cette formule nécessite une validation matricielle avec [Ctrl]+[Maj]+[Entrée], ou nous pouvons utiliser à la place SOMMEPROD qui fonctionne sans manipulation spéciale.

=SOMMEPROD(CNUM(B2:B6))

4. Application aux codes employés

Pour nos codes employés qui contiennent des lettres, la logique est similaire mais légèrement différente. Dans notre exemple, E45 doit devenir 00E45. Nous ne pouvons pas utiliser directement la fonction TEXTE car nos codes contiennent la lettre E.

La solution la plus simple consiste à utiliser l'opérateur de concaténation :

="00"&C2

Cette formule prend littéralement le texte "00" et le colle devant le contenu de C2.

Nous validons avec [Entrée] et nous obtenons 00E45.

Nous appliquons cette formule aux autres lignes en double-cliquant sur la poignée de recopie, puis nous effectuons le même processus de copie en valeur que précédemment pour remplacer les valeurs originales.