Ce tutoriel montre différentes façons d’ajouter des zéros de tête dans Excel: Comment conserver les zéros pendant que vous tapez, Afficher les zéros de tête dans les cellules, supprimer ou masquer les zéros.
Si vous utilisez Excel non seulement pour calculer des nombres, mais aussi pour conserver des enregistrements tels que des codes postaux, des numéros de sécurité ou des identifiants d’employés, vous devrez peut-être conserver des zéros en tête dans les cellules. Toutefois, si vous essayez de taper un code postal comme « 00123 » dans une cellule, Excel immédiatement tronquer à « 123 ».,
Le fait est que Microsoft Excel traite les codes postaux, les numéros de téléphone et autres entrées similaires comme des numéros, leur applique le format général ou numérique et supprime automatiquement les zéros précédents. Heureusement, Excel fournit également les moyens de continuer à diriger des zéros dans les cellules, et plus loin dans ce tutoriel, vous trouverez une poignée de façons de le faire.,
- Gardez les zéros de tête dans Excel pendant que vous tapez
- afficher les zéros de tête en utilisant un format personnalisé
- ajouter des zéros de tête aux nombres (fonction texte)
- Pad chaînes de texte avec des zéros
- Ajouter un nombre fixe de zéros précédents
- supprimer les zéros de tête dans Tapez
pour commencer, voyons comment vous pouvez mettre 0 devant un nombre dans Excel, par exemple tapez 01 dans une cellule., Pour cela, changez simplement le format de cellule en texte:
- sélectionnez la ou les cellules où vous souhaitez préfixer les nombres avec 0.
- accédez à L’onglet Accueil > groupe de numéros et sélectionnez Texte dans la zone Format des numéros.
dès que vous tapez un zéro avant le nombre, Excel affichera un petit triangle vert dans le coin supérieur gauche de la cellule indiquant que quelque chose ne va pas avec le contenu de la cellule. Pour supprimer cet indicateur d’erreur, sélectionnez la ou les cellules, cliquez sur le signe d’avertissement, puis cliquez sur Ignorer L’erreur.,
La capture d’écran suivante montre le résultat:
une Autre façon de garder les zéros non significatifs dans Excel est préfixe d’un nombre avec une apostrophe (‘). Par exemple, au lieu de taper 01, tapez ’01. Dans ce cas, vous n’avez pas besoin de changer le format de la cellule.
Bottom line: cette technique simple a une limitation importante-la valeur résultante est une chaîne de texte, pas un nombre, et par conséquent elle ne peut pas être utilisée dans les calculs et les formules numériques., Si ce n’est pas ce que vous voulez, modifiez uniquement la représentation visuelle de la valeur en appliquant un format numérique personnalisé comme illustré dans l’exemple suivant.
comment afficher les zéros principaux dans Excel à l’aide d’un format numérique personnalisé
pour afficher les zéros principaux, appliquez un format numérique personnalisé en procédant comme suit:
- sélectionnez une ou plusieurs cellules où vous souhaitez afficher les zéros principaux, puis appuyez sur Ctrl+1 pour ouvrir la boîte de dialogue Formater les cellules.
- Sous Catégorie, sélectionnez Personnalisé.
- tapez un code DE format dans la zone Type.,
dans la plupart des cas, vous aurez besoin d’un code de format composé de 0 espaces réservés, comme 00. Le nombre de zéros dans le format de code correspond au nombre total de chiffres que vous souhaitez afficher dans une cellule (vous trouverez quelques exemples ci-dessous).
- Cliquez sur OK pour enregistrer les modifications.
par exemple, pour ajouter des zéros principaux pour créer un nombre à 5 chiffres, utilisez le code de format suivant: 00000
En utilisant les formats de nombres personnalisés Excel, vous pouvez ajouter des zéros principaux pour créer des nombres de longueur fixe, comme dans l’exemple ci-dessus, et des nombres de longueur variable., Tout se résume à la qui espace réservé que vous utilisez dans le format de code:
- 0 – affiche des zéros supplémentaires
- # – ne pas afficher les zéros supplémentaires
Par exemple, si vous appliquez la 000# format d’une cellule, tout numéro que vous tapez dans la cellule aura jusqu’à 3 zéros.
vos formats numériques personnalisés peuvent également inclure des espaces, des tirets, des parenthèses, etc. L’explication détaillée peut être trouvée ici: format de numéro Excel personnalisé.
la feuille de calcul suivante donne quelques autres exemples de formats personnalisés pouvant afficher des zéros en tête dans Excel.,
et les codes de format suivants peuvent être utilisés pour afficher les numéros dans des formats spéciaux tels que les codes postaux américains, les numéros de téléphone, les numéros de carte de crédit et les numéros de sécurité sociale.,
D 1 Custom format Typed number Displayed number 2 Zip code 00000 1234 01234 3 Social security 000-00-0000 12345678 012-34-5678 4 Credit card 0000-0000-0000-0000 12345556789123 0012-3455-5678-9123 5 Phone numbers 00-0-000-000-0000 12345556789 00-1-234-555-6789 Tip., Excel a quelques formats spéciaux prédéfinis pour les codes postaux, les numéros de téléphone et les numéros de sécurité sociale, comme indiqué dans la capture d’écran ci-dessous:
Bottom line: cette méthode est préférable d’être utilisé dans les situations où vous travaillez avec un ensemble de données numériques et les résultats Il ne change que l’affichage d’un nombre, mais pas le nombre lui-même: les zéros en tête apparaissent dans les cellules, la valeur réelle s’affiche dans la barre de formule. Lorsque vous référencez de telles cellules dans des formules, les calculs sont parfumés avec les valeurs d’origine., Les formats personnalisés ne peuvent être appliqués qu’aux données numériques (nombres et dates) et le résultat est également un nombre ou une date.
comment ajouter des zéros de début dans Excel avec la fonction TEXT
alors qu’un format numérique personnalisé affiche zéro devant un nombre sans changer réellement la valeur sous-jacente, la fonction de texte Excel pads nombres avec des zéros en insérant « physiquement » des zéros de début dans les cellules.
pour ajouter des zéros avec une formule TEXT(value, format_text), vous utilisez les mêmes codes de format que dans les formats numériques personnalisés., Cependant, le résultat de la fonction TEXT est toujours une chaîne de texte, même si elle ressemble beaucoup à un nombre.
par exemple, pour insérer 0 avant une valeur dans la cellule A2, utilisez cette formule:
=TEXT(A2, "0#")pour créer une chaîne préfixée zéro d’une longueur fixe, disons une chaîne de 5 caractères, Utilisez celle-ci:
=TEXT(A2, "000000")Veuillez faire attention que la fonction Texte nécessite d’inclure les codes de format entre guillemets.,ike in Excel:
A B C 1 Original number Padded number Formula 2 1 01 =TEXT(B2, « 0# ») 3 12 12 =TEXT(B3, « 0# ») 4 1 00001 =TEXT(B4, »00000″) 5 12 00012 =TEXT(B5, »00000″) For more information about Text formulas, please see How to use the TEXT function in Excel.,
Bottom line: la fonction de texte Excel renvoie toujours une chaîne de texte, pas un nombre, et vous ne pourrez donc pas utiliser les résultats dans les calculs arithmétiques et autres formules, sauf si vous devez comparer la sortie avec d’autres chaînes de texte.
comment ajouter des zéros aux chaînes de texte
dans les exemples précédents, vous avez appris à ajouter zéro avant un nombre dans Excel. Mais que faire si vous devez mettre zéro (s) devant une chaîne de texte comme 0A102? Dans ce cas, ni le texte ni le format personnalisé ne fonctionneront car ils ne traitent que des valeurs numériques.,
Si la valeur à compléter par zéro contient des lettres ou d’autres caractères de texte, utilisez l’une des formules suivantes, qui offrent une solution universelle applicable à la fois aux nombres et aux chaînes de texte.
Formule 1. Ajouter des zéros de tête en utilisant la bonne fonction
la façon la plus simple de placer des zéros de tête avant les chaînes de texte dans Excel est d’utiliser la bonne fonction:
RIGHT(« 0000″& cell, string_length)où:
- « 0000 » est le nombre maximum de zéros que vous souhaitez ajouter. Par exemple, pour ajouter 2 zéros, vous tapez « 00 ».,
- Cellule est une référence à la cellule contenant la valeur d’origine.
- String_length est le nombre de caractères que la chaîne résultante doit contenir.
par exemple, pour créer une chaîne de 6 caractères préfixée par zéro basée sur une valeur dans la cellule A2, utilisez cette formule:
=RIGHT("000000"&A2, 6)la formule ajoute 6 zéros à la valeur dans A2 (« 000000″&A2), puis extrayez les 6 bons caractères., En conséquence, il insère juste le bon nombre de zéros pour atteindre la limite de chaîne totale spécifiée:
dans l’exemple ci-dessus, le nombre maximum de zéros est égal à la longueur totale de la chaîne (6 caractères), et donc toutes les chaînes résultantes sont longues de 6 caractères (longueur fixe). Si elle est appliquée à une cellule vide, la formule renvoie une chaîne composée de 6 zéros.,
Selon une logique d’entreprise, vous pouvez fournir différents nombres de zéros et de nombre de caractères, par exemple:
=RIGHT("00"&A2, 6)Comme le résultat, vous obtiendrez des chaînes de longueur variable qui contiennent jusqu’à 2 zéros en tête:
la Formule 2.,ore une chaîne de texte dans Excel utilise cette combinaison de fonctions REPT et LEN:
REPT(0, Nombre de zéros-Len(cell))&cellPar exemple, pour ajouter des zéros principaux à la valeur dans A2 pour créer une chaîne de 6 caractères, cette formule est la suivante:
=REPT(0, 6-LEN(A2))&A2comment fonctionne cette formule:
sachant que la fonction REPT Répète un caractère donné un nombre spécifié de fois et que Len renvoie la longueur totale de la chaîne, la logique de la formule est facile à comprendre:
- len(A2) obtient le nombre total de caractères dans la cellule A2.,
- REPT (0, 6-LEN (A2)) ajoute le nombre de zéros requis. Pour calculer le nombre de zéros à ajouter, vous soustrayez la longueur de la chaîne dans A2 du nombre maximum de zéros.
- enfin, vous concaténez des zéros avec la valeur A2, et obtenez le résultat suivant:
Bottom line: cette formule peut ajouter des zéros de début aux nombres et aux chaînes de texte, mais le résultat est toujours du texte, pas du nombre.,
comment ajouter un nombre fixe de zéros précédents
pour préfixer toutes les valeurs d’une colonne (nombres ou chaînes de texte) avec un certain nombre de zéros, utilisez la fonction CONCATENATE ou l’opérateur Esperluette.,
Par exemple, pour mettre 0 avant un nombre dans la cellule A2, utilisez l’une des formules suivantes:
=CONCATENATE(0,A2)ou
=0&A2comme indiqué dans la capture d’écran ci-dessous, la formule ajoute un seul zéro à toutes les cellules d’une colonne, caractères la valeur d’origine contient:
de la même manière, vous pouvez insérer 2 zéros (00), 3 zéros (000) ou autant de zéros que vous le souhaitez avant les nombres et les chaînes de texte.,
Bottom line: le résultat de cette formule est également une chaîne de texte même lorsque vous concaténez des zéros avec des nombres.
comment supprimer les zéros de tête dans Excel
la méthode que vous utilisez pour supprimer les zéros de tête dans Excel dépend de la façon dont ces zéros ont été ajoutés:
- Si les zéros précédents ont été ajoutés avec un format numérique personnalisé (les zéros sont visibles dans une cellule, mais pas dans,
- Si des zéros ont été tapés ou saisis dans des cellules formatées en texte (un petit triangle vert est affiché dans le coin supérieur gauche de la cellule), convertissez le texte en nombre.
- Si des zéros en tête ont été ajoutés à l’aide d’une formule (la formule apparaît dans la barre de formule lorsque la cellule est sélectionnée), utilisez la fonction VALUE pour les supprimer.,
l’image suivante montre les trois cas pour vous aider à choisir la bonne technique:
supprimer les zéros de tête en changeant le format de cellule
Si les zéros de tête sont affichés dans les cellules avec un format personnalisé, puis changer le format de cellule à nouveau par défaut général, ou appliquer un autre format de nombre qui n’affiche pas les zéros précédents.,
supprimer les zéros en tête en convertissant le texte en nombre
lorsque des zéros préfixés apparaissent dans une cellule au format texte, le moyen le plus simple de les supprimer est de sélectionner la ou les cellules, de cliquer sur le point d’exclamation, puis de cliquer sur Convertir en nombre:
Supprimer zéros en utilisant une formule
dans le cas où un ou des Zéro(s) précédent(s) est ajouté (S) avec une formule, utilisez une autre formule pour le supprimer. La formule de suppression de Zéro est aussi simple que:
=VALUE(A2)où A2 est la cellule à partir de laquelle vous souhaitez supprimer les zéros précédents.,
Cette méthode peut également être utilisée pour se débarrasser des zéros tapés directement dans les cellules (comme dans l’exemple précédent) ou importés dans Excel à partir d’une source externe. Dans l’ensemble, si vous avez affaire à une chaîne préfixée par zéro qui représente un nombre, vous pouvez utiliser la fonction VALUE pour convertir le texte en nombre et supprimer les zéros de début en cours de route.
la capture d’écran suivante montre deux formules:
- La formule de texte en B2 ajoute des zéros à la valeur en A2, et
- la formule de valeur en C2 supprime les zéros en tête de la valeur en B2.,
comment masquer les zéros dans Excel
Si vous ne souhaitez pas afficher les valeurs zéro dans votre feuille Excel, vous avez les deux options suivantes:
- pour masquer les zéros sur toute la feuille, décochez L’option Afficher un zéro dans les cellules ayant une valeur zéro., Pour cela, cliquez sur Fichier > Options > avancé, et faites défiler jusqu’aux options D’affichage de cette section de feuille de calcul:
- Pour masquer les valeurs zéro dans certaines cellules, appliquez le format numérique personnalisé suivant à ces cellules: #;#;;@
pour cela, sélectionnez les cellules où vous souhaitez masquer les zéros, cliquez sur Ctrl+1 pour ouvrir la boîte de dialogue Formater les cellules, sélectionnez Personnalisé sous catégorie et tapez le code de format ci-dessus dans la zone type.,
la capture d’écran ci-dessous montre que la cellule B2 contient une valeur zéro, mais elle n’est pas affichée dans la cellule:
Voici comment vous pouvez ajouter, supprimer et masquer des zéros dans Excel. Pour mieux comprendre les techniques décrites dans ce tutoriel, vous pouvez télécharger L’exemple de classeur Excel Leading Zeros. Je vous remercie pour la lecture et j’espère vous voir sur notre blog la semaine prochaine.,
- Comment formater des cellules dans Excel
- format de numéro Excel personnalisé
- Comment convertir la date en texte à L’aide de la fonction de texte Excel et de méthodes sans formule
- Comment échanger des colonnes dans Excel par glisser-déposer et d’autres façons de déplacer des colonnes
