Comment ajouter des zéros non significatifs dans Excel - Tout ce que vous devez savoir

Il existe des situations où vous devez ajouter des zéros non significatifs à un jeu de données dans Excel. Cela peut être le cas si vous conservez des enregistrements dans Excel, tels que des identifiants d'employé ou des identifiants de transaction.

Par exemple, vous souhaiterez peut-être obtenir un aspect cohérent de votre ensemble de données, comme indiqué ci-dessous :

Dans ce didacticiel, vous apprendrez différentes manières d'ajouter des zéros non significatifs dans Excel :

• Conversion du format en texte
• Utilisation du formatage numérique personnalisé
• Utilisation de la fonction de texte
• Utilisation des fonctions REPT/LEN
• Utiliser VBA

Chacune de ces méthodes a des avantages et des inconvénients (couverts dans chaque section).

Voyons comment chacun d'eux fonctionne.

Ajouter des zéros non significatifs en convertissant le format en texte

Quand utiliser: Lorsque vous disposez d'un petit ensemble de données numériques et que vous prévoyez d'effectuer cette modification manuellement.

Supposons que vous ayez des identifiants d'employés du service marketing comme indiqué ci-dessous et que vous souhaitiez rendre ces identifiants cohérents en ajoutant des zéros non significatifs.

Vous essayez donc de changer l'identifiant en entrant des zéros non significatifs (0001 au lieu de 1).

Mais à votre grand étonnement, Excel le reconvertit en 1.

Cela se produit car Excel comprend que 00001 et 1 sont les mêmes nombres et doivent suivre les mêmes règles d'affichage.

Aussi frustrant que cela puisse être pour vous, Excel a ses raisons.

Donc, pour faire le travail sans contourner les règles d'Excel, vous devrez profiter du fait que cette règle ne s'applique pas à la mise en forme du texte.

Voici donc ce que vous devez faire :

1. Sélectionnez les cellules dans lesquelles vous souhaitez ajouter manuellement des zéros non significatifs.
2. Accédez à Accueil → Groupe de numéros et sélectionnez Texte dans la liste déroulante.

C'est ça!

Désormais, lorsque vous entrez manuellement des zéros non significatifs, Excel s'y conformera facilement.

Attention : lorsque vous convertissez le format en texte, certaines fonctions Excel ne fonctionneront pas correctement. Par exemple, la fonction SUM/COUNT ignorerait la cellule car elle est au format texte.

Ajouter des zéros non significatifs à l'aide d'un formatage numérique personnalisé

Quand utiliser: lorsque vous disposez d'un ensemble de données numériques et que vous souhaitez que le résultat soit numérique (pas de texte).

Lorsque vous affichez un nombre dans un format spécifique, cela ne modifie pas la valeur sous-jacente du nombre. Par exemple, je peux afficher le nombre 1000 sous la forme 1000 ou 1000 ou 1000,00 ou 001000 ou 26-09-1902 (les dates paires sont des nombres dans le backend dans Excel).

Dans toutes les différentes manières d'afficher le nombre, la valeur du nombre ne change jamais. C'est seulement la façon dont il est affiché qui est modifié.

Pour ajouter des zéros non significatifs, nous pouvons le formater pour le montrer de cette façon, tandis que la valeur sous-jacente resterait inchangée.

Voici les étapes à suivre pour utiliser cette technique pour ajouter des zéros non significatifs dans Excel :

1. Sélectionnez les cellules dans lesquelles vous souhaitez ajouter des zéros non significatifs.
2. Allez dans Accueil → Groupe de numéros et cliquez sur le lanceur de dialogue (une petite flèche inclinée en bas à droite). Cela ouvrira la boîte de dialogue Formater les cellules. Alternativement, vous pouvez également utiliser le raccourci clavier : Ctrl + 1.
3. Dans la boîte de dialogue Format de cellule, dans l'onglet Nombre, sélectionnez Personnalisé dans la liste Catégorie.
4. Dans le champ Type, saisissez 00000
5. Cliquez sur OK.

Faire cela affichera toujours tous les nombres sous forme de cinq chiffres, où les 0 de tête sont automatiquement ajoutés si le nombre est inférieur à 5 chiffres. Donc 10 deviendrait 00010 et 100 deviendrait 00100.

Dans ce cas, nous avons utilisé six zéros, mais si vos données ont des nombres avec plus de chiffres, vous devez utiliser le format en conséquence.

Remarque : Cette technique ne fonctionnerait que pour un ensemble de données numériques. Si vous avez des identifiants d'employé tels que A1, A2, A3 et ainsi de suite, il s'agit de texte et ne changeront pas lorsque vous appliquerez le format personnalisé comme indiqué ci-dessus.

Ajouter des zéros non significatifs à l'aide de la fonction TEXTE

Quand utiliser: Lorsque vous voulez que le résultat soit du texte.

La fonction TEXTE vous permet de modifier la valeur au format souhaité.

Par exemple, si vous voulez que 1 s'affiche sous la forme 001, vous pouvez utiliser la fonction TEXTE pour cela.

Cependant, n'oubliez pas que la fonction TEXTE changerait le format et en ferait TEXTE. Cela signifie que lorsque vous définissez 1 comme 001, Excel traite le nouveau résultat comme du texte avec trois caractères (tout comme abc ou xyz).

Voici comment ajouter des zéros non significatifs à l'aide de la fonction TEXTE :

1. Si vous avez les nombres dans la colonne A (disons de A2:A100), sélectionnez B2:B100 et entrez la formule suivante :
   = TEXTE (A2, " 00000 ")
2. Appuyez sur Ctrl + Entrée pour appliquer la formule à toutes les cellules sélectionnées.

Cela affichera tous les nombres sous forme de cinq chiffres, où les 0 de début sont automatiquement ajoutés si le nombre est inférieur à 5 chiffres.

L'un des avantages de la conversion de données en texte est que vous pouvez désormais l'utiliser dans des formules de recherche telles que RECHERCHEV ou INDEX/MATCH pour récupérer les détails d'un employé à l'aide de son identifiant d'employé.

Remarque : Cette technique ne fonctionnerait que pour un ensemble de données numériques. Si vous avez des identifiants d'employé tels que A1, A2, A3 et ainsi de suite, il s'agit de texte et ne changeront pas lorsque vous appliquerez le format personnalisé comme indiqué ci-dessus..

Ajouter des zéros non significatifs à l'aide des fonctions REPT et LEN

Quand utiliser: Lorsque vous disposez d'un ensemble de données numérique/alphanumérique et que vous souhaitez que le résultat soit du texte.

L'inconvénient de l'utilisation de la fonction TEXTE était qu'elle ne fonctionnait qu'avec des données numériques. Mais si vous avez un ensemble de données alphanumériques (disons A1, A2, A3, etc.), la fonction TEXTE échouera.

Dans de tels cas, une combinaison des fonctions REPT et LEN fait l'affaire.

Voici comment faire:

1. Si vous avez les nombres dans la colonne A (disons de A2:A100), sélectionnez B2:B100 et entrez la formule suivante :
   =REPT(0,5-LEN(A2))&A2
2. Appuyez sur Ctrl + Entrée pour appliquer la formule à toutes les cellules sélectionnées.

Cela ferait tous les nombres/chaînes de 5 caractères avec des zéros non significatifs partout où cela est nécessaire.

Voici comment fonctionne cette formule :

• LEN(A2) donne la longueur de la chaîne/des nombres dans la cellule.
• =REPT(0,5-LEN(A2)) donnerait le nombre de 0 qui devrait être ajouté. Ici, j'ai utilisé 5 dans la formule car c'était la longueur maximale de la chaîne/des nombres dans mon ensemble de données. Vous pouvez modifier cela en fonction de vos données.
• =REPT(0,5-LEN(A2))&A2 ajouterait simplement le nombre de zéros à la valeur de la cellule. Par exemple, si la valeur dans la cellule est 123, cela renverrait 00123.

Ajouter des zéros non significatifs à l'aide de la fonction personnalisée (VBA)

Si l'ajout de zéros non significatifs dans Excel est quelque chose que vous devez faire assez souvent, l'utilisation d'une fonction personnalisée est une bonne idée.

Voici le code VBA qui créera une fonction simple pour ajouter des zéros non significatifs :

'Code par Sumit Bansal de http://trumpexcel.com Fonction AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid(ref, i, 1) Else Result = "0" & ​​Result End If Next i AddLeadingZeroes = Result End Function

Ajoutez simplement ce code à la fenêtre de code du module et vous pourrez l'utiliser comme n'importe quelle autre fonction de feuille de calcul.

Ou créez un complément pour celui-ci et partagez-le avec vos collègues.