Ce didacticiel couvre les différentes méthodes pour ajouter ou conserver des zéros non significatifs ainsi que pour supprimer des zéros non significatifs dans Excel.
Chaque fois que vous entrez ou importez des nombres avec un ou plusieurs zéros non significatifs, comme 000652, Excel supprime automatiquement ces zéros et seul le nombre lui-même s'affiche dans les cellules (652). En effet, les zéros non significatifs ne sont pas nécessaires pour les calculs et ne comptent pas.
Cependant, il y a des moments où ces zéros non significatifs sont nécessaires, comme lorsque vous entrez des numéros d'identification, des numéros de téléphone, des numéros de carte de crédit, des codes de produit ou des codes postaux, etc. Heureusement, Excel nous propose plusieurs façons d'ajouter ou de conserver des zéros non significatifs. dans les cellules. Dans cet article, nous vous montrerons les différentes manières d'ajouter ou de conserver des zéros non significatifs et de supprimer des zéros non significatifs.
Ajout de zéros non significatifs dans Excel
Essentiellement, il existe 2 méthodes que vous pouvez utiliser pour ajouter des zéros non significatifs : un, formatez votre nombre en tant que « Texte » ; Deuxièmement, utilisez une mise en forme personnalisée pour ajouter des zéros non significatifs. La méthode que vous souhaitez utiliser peut dépendre de ce que vous voulez faire avec le numéro.
Vous voudrez peut-être ajouter un zéro non significatif lorsque vous entrez des numéros d'identification uniques, des numéros de compte, des numéros de sécurité sociale ou des codes postaux, etc. des nombres au texte. Vous ne feriez jamais la somme ou la moyenne des numéros de téléphone ou des numéros de compte.
Il existe plusieurs façons d'ajouter ou de compléter des zéros avant les nombres en les formatant sous forme de texte :
- Changer le format de cellule en Texte
- Ajouter une apostrophe (‘)
- Utilisation de la fonction TEXTE
- Utilisation de la fonction REPT/LEN
- Utiliser la fonction CONCATENATE/l'opérateur esperluette (&)
- Utilisation de la fonction DROITE
Modification du format de cellule en texte
C'est l'un des moyens les plus simples d'ajouter des zéros non significatifs à vos nombres. Si vous vous contentez de saisir des chiffres et que vous souhaitez conserver les zéros non significatifs pendant la saisie, cette méthode est faite pour vous. En changeant le format de cellule de Général ou Nombre en Texte, vous pouvez forcer Excel à traiter vos nombres comme des valeurs de texte et tout ce que vous tapez dans la cellule restera exactement le même. Voici comment procéder :
Sélectionnez la ou les cellules dans lesquelles vous souhaitez ajouter des zéros non significatifs. Accédez à l'onglet « Accueil », cliquez sur la liste déroulante « Format » dans le groupe Nombres et sélectionnez « Texte » dans les options de format.
Désormais, lorsque vous tapez vos nombres, Excel ne supprime aucun zéro non significatif.
Vous pouvez voir un petit triangle vert (indicateur d'erreur) dans le coin supérieur gauche de la cellule et lorsque vous sélectionnez cette cellule, il vous montrera un panneau d'avertissement indiquant que vous avez stocké le nombre sous forme de texte.
Pour supprimer le message d'erreur, sélectionnez la ou les cellules, cliquez sur le signe d'avertissement, puis sélectionnez « Ignorer l'erreur » dans la liste.
Vous pouvez également taper des numéros de téléphone avec un espace ou un trait d'union entre les numéros, Excel traitera automatiquement ces numéros comme du texte.
Utiliser l'interlignage Apostrophe ( ‘ )
Une autre façon d'ajouter des zéros non significatifs dans Excel consiste à ajouter une apostrophe (‘) au début du nombre. Cela forcera Excel à saisir un nombre sous forme de texte.
Tapez simplement une apostrophe avant les chiffres et appuyez sur « Entrée ». Excel laissera les zéros non significatifs, mais le (‘) ne sera pas visible dans la feuille de calcul, sauf si vous sélectionnez la cellule.
Utilisation de la fonction de texte
La méthode ci-dessus ajoute des zéros aux nombres au fur et à mesure que vous les tapez, mais si vous avez déjà une liste de nombres et que vous souhaitez remplir les zéros avant eux, la fonction TEXTE est la bonne méthode pour vous. La fonction TEXTE vous permet de convertir des nombres en chaînes de texte tout en appliquant un formatage personnalisé.
La syntaxe de la fonction TEXTE :
= TEXTE( valeur, format_texte)Où,
- valeur - C'est la valeur numérique que vous devez convertir en texte et appliquer la mise en forme.
- format_texte – est le format que vous souhaitez appliquer.
Avec la fonction TEXTE, vous pouvez spécifier le nombre de chiffres que votre numéro doit avoir. Par exemple, si vous voulez que vos nombres comportent 8 chiffres, tapez 8 zéros dans le deuxième argument de la fonction : « 00000000 ». Si vous avez un nombre à 6 chiffres dans une cellule, la fonction ajoutera manuellement 2 zéros non significatifs et si vous avez des nombres à 2 chiffres comme 56, le reste sera des zéros (00000056).
Par exemple, pour ajouter des zéros non significatifs et allonger les nombres à 6 chiffres, utilisez cette formule :
=TEXTE(A2, "000000")Comme nous avons 6 zéros dans le deuxième argument de la formule, la fonction convertit la chaîne numérique en une chaîne de texte et ajoute 5 zéros non significatifs pour que la chaîne soit longue de 6 chiffres.
Noter: N'oubliez pas de mettre les codes de format entre guillemets doubles dans la fonction.
Vous pouvez maintenant appliquer la même formule au reste des cellules en faisant glisser la poignée de remplissage. Comme vous pouvez le voir, la fonction convertit les nombres en textes et ajoute des zéros non significatifs aux nombres de sorte que le nombre total de chiffres soit de 6.
TEXT Function renverra toujours la valeur sous forme de chaîne de texte, pas de nombre, vous ne pourrez donc pas les utiliser dans des calculs arithmétiques, mais vous pouvez toujours les utiliser dans des formules de recherche telles que RECHERCHEV ou INDEX/MATCH pour récupérer les détails d'un produit à l'aide des ID de produit.
Utilisation de la fonction CONCATENATE/opérateur esperluette (&)
Si vous souhaitez ajouter un nombre fixe de zéros non significatifs avant tous les nombres d'une colonne, vous pouvez utiliser la fonction CONCATENATE ou l'opérateur esperluette (&).
Syntaxe de la fonction CONCATENATE :
=CONCATENER(texte1, [texte2], ...)Où,
texte 1 - Le nombre de zéros à insérer avant le nombre.
texte2 - Le numéro d'origine ou la référence de la cellule
Syntaxe de l'opérateur esperluette :
=Valeur_1 & Valeur_2 Où,
Value_1 est le premier zéro à insérer avant le nombre et Value_2 est le nombre.
Par exemple, pour ajouter seulement deux zéros avant un nombre, utilisez l'une de ces formules :
=CONCATENER("00",A2)Le premier argument est deux zéros ("00") parce que nous voulons remplir deux zéros avant le nombre en A2 (qui est le deuxième argument).
Ou,
="00"&A2Ici, le premier argument est 2 zéros, suivi de l'opérateur '&', et le deuxième argument est le nombre.
Comme vous pouvez le voir, la formule n'ajoute que deux zéros non significatifs à tous les nombres d'une colonne, quel que soit le nombre de chiffres contenus dans le nombre.
Ces deux formules joignent un certain nombre de zéros avant les nombres d'origine et les stockent sous forme de chaînes de texte.
Utilisation de la fonction REPT/LEN
Si vous souhaitez ajouter des zéros non significatifs aux données numériques ou alphanumériques et convertir la chaîne en texte, utilisez la fonction REPT. La fonction REPT est utilisée pour répéter un ou plusieurs caractères un certain nombre de fois. Cette fonction peut également être utilisée pour insérer des nombres fixes de zéros non significatifs avant le nombre.
=REPT(texte, nombre_fois)Où 'text' est le caractère que nous voulons répéter (dans notre cas '0') et l'argument 'number_times' est le nombre de fois que nous voulons répéter ce caractère.
Par exemple, pour générer cinq zéros avant les nombres, la formule ressemblerait à :
=REPT(0,5)&A2La formule répète 5 zéros et joint la chaîne numérique en A2 et renvoie le résultat. Ensuite, la formule est appliquée à la cellule B2:B6 à l'aide de la poignée de recopie.
La formule ci-dessus ajoute un nombre fixe de zéros avant le nombre, mais la longueur totale du nombre varie en fonction du nombre.
Si vous souhaitez ajouter des zéros non significatifs partout où cela est nécessaire pour créer des chaînes de caractères spécifiques (longueur fixe), vous pouvez utiliser les fonctions REPT et LEN ensemble.
Syntaxe:
=REPT(texte, nombre_fois-LEN(texte))&cellulePar exemple, pour ajouter des zéros préfixés à la valeur dans A2 et créer une chaîne longue de 5 caractères, essayez cette formule :
=REPT(0,5-LEN(A2))&A2Ici, 'LEN(A2)' obtient le total de la longueur de la chaîne/des nombres dans la cellule A2. « 5 » est la longueur maximale de la chaîne/des nombres que la cellule devrait avoir. Et la partie ‘REPT(0,5-LEN(A2))’ ajoute le nombre de zéros en soustrayant la longueur de la chaîne dans A2 du nombre maximum de zéros (5). Ensuite, un nombre de 0 est joint avant la valeur de A2 pour créer une chaîne de longueur fixe.
Utilisation de la fonction DROITE
Une autre façon de remplir les zéros non significatifs avant une chaîne dans Excel consiste à utiliser la fonction DROITE.
La fonction RIGHT peut ajouter un certain nombre de zéros au début d'un nombre et extraire les N caractères les plus à droite de la valeur.
Syntaxe:
= DROIT (texte, num_chars)- texte est la cellule ou la valeur dont vous souhaitez extraire les caractères.
- nombre_caractères est le nombre de caractères à extraire du texte. Si cet argument n'est pas fourni, alors seul le premier caractère sera extrait.
Pour cette méthode, nous concaténons le nombre maximal de zéros avec la référence de cellule qui contient la chaîne dans l'argument « texte ».
Pour créer un nombre à 6 chiffres basé sur la chaîne numérique en A avec des zéros non significatifs, essayez cette formule :
=DROITE("0000000"&A2,6)Le premier argument (texte) de la formule ajoute 7 zéros à la valeur dans A2 ("0000000" & A2), puis renvoie les 7 caractères les plus à droite, ce qui donne des zéros non significatifs.
Ajout de zéros non significatifs à l'aide d'un formatage numérique personnalisé
Si vous utilisez l'une des méthodes ci-dessus pour placer des zéros non significatifs avant les nombres, vous obtiendrez toujours une chaîne de texte, pas un nombre. Et ils ne seront pas très utiles dans les calculs ou dans les formules numériques.
La meilleure façon d'ajouter des zéros non significatifs dans Excel consiste à appliquer une mise en forme numérique personnalisée. Si vous ajoutez des zéros non significatifs en ajoutant un format de nombre personnalisé à la cellule, cela ne change pas la valeur de la cellule mais uniquement la façon dont elle est affichée. La valeur restera toujours sous forme de nombre, pas de texte.
Pour modifier la mise en forme des nombres de cellules, procédez comme suit :
Sélectionnez la cellule ou la plage de cellules où vous souhaitez afficher les zéros non significatifs. Ensuite, cliquez avec le bouton droit n'importe où dans cette plage sélectionnée et sélectionnez l'option « Formater les cellules » dans le menu contextuel. Ou appuyez sur les touches de raccourci Ctrl + 1.
Dans la fenêtre Format de cellule, accédez à l'onglet « Nombre » et sélectionnez « Personnalisé » dans les options de catégorie.
Entrez le nombre de zéros dans la case « Type : » pour spécifier le nombre total de chiffres que vous souhaitez afficher dans une cellule. Par exemple, si vous souhaitez que le numéro soit composé de 6 chiffres, saisissez « 000000 » comme code de format personnalisé. Ensuite, cliquez sur « OK » pour postuler.
Cela affichera des zéros non significatifs avant les nombres et si le nombre est inférieur à 6 chiffres, il remplira le zéro avant.
Les nombres n'apparaîtront qu'avec des zéros non significatifs tandis que la valeur sous-jacente restera inchangée. Si vous sélectionnez une cellule avec une mise en forme personnalisée, elle vous montrera le numéro d'origine dans la barre de formule
Il existe de nombreux espaces réservés numériques que vous pouvez utiliser dans votre format de nombre personnalisé. Mais il n'y a que deux espaces réservés principaux que vous pouvez utiliser pour ajouter des zéros non significatifs dans les nombres.
- 0 – C'est l'espace réservé aux chiffres qui affiche des zéros supplémentaires. Il affiche les chiffres forcés 0-9, que le chiffre soit ou non pertinent pour la valeur. Par exemple, si vous tapez 2.5 avec le code de format 000.00, il affichera 002.50.
- # – Il s'agit de l'espace réservé aux chiffres qui affiche les chiffres facultatifs et n'inclut pas les zéros supplémentaires. Par exemple, si vous tapez 123 avec le code de formatage 000#, il affichera 0123.
De plus, tout signe de ponctuation ou autre caractère que vous incluez dans le code de format sera affiché tel quel. Vous pouvez utiliser des caractères tels que tiret (-), virgule (,), barre oblique (/), etc.
Par exemple, vous pouvez également formater des nombres en tant que numéros de téléphone en utilisant un format personnalisé.
La boîte de dialogue Formater le code dans la boîte de dialogue Formater les cellules :
Le résultat:
Appliquons ce code de formatage dans l'exemple suivant :
##0000
Comme vous pouvez le voir, « 0 » ajoutera des zéros supplémentaires tandis que « # » n'ajoutera pas de zéros insignifiants :
Vous pouvez également utiliser des codes de format prédéfinis dans la section « Formats spéciaux » de la boîte de dialogue Formater les cellules pour les codes postaux, les numéros de téléphone et les numéros de sécurité sociale.
Le tableau suivant montre des nombres avec des zéros non significatifs où différents codes de format « spécial » sont appliqués à différentes colonnes :
Supprimer les zéros non significatifs dans Excel
Maintenant, vous avez appris à ajouter des zéros non significatifs dans Excel, voyons comment supprimer les zéros non significatifs du nombre de chaînes. Parfois, lorsque vous importez des données à partir d'une source externe, les nombres peuvent finir par avoir des zéros de préfixe et être formatés en texte. Dans de tels cas, vous devez supprimer les zéros non significatifs et les reconvertir en nombres afin de pouvoir les utiliser dans des formules.
Il existe différentes manières de supprimer les zéros non significatifs dans Excel et nous les verrons un par un.
Supprimer les zéros non significatifs en modifiant la mise en forme des cellules
Si des zéros non significatifs ont été ajoutés par un formatage numérique personnalisé, vous pouvez facilement les supprimer en modifiant le format des cellules. Vous pouvez savoir si vos cellules sont formatées de manière personnalisée en regardant la barre d'adresse (les zéros seront visibles dans la cellule et non dans la barre d'adresse).
Pour supprimer les zéros préfixés, sélectionnez les cellules avec les zéros non significatifs, cliquez sur la case "Format des nombres" et sélectionnez l'option de formatage "Général" ou "Nombre".
Maintenant, les zéros non significatifs ont disparu :
Supprimer les zéros non significatifs en convertissant le texte en nombres
Si vos zéros non significatifs ont été ajoutés en modifiant le format de cellule ou en ajoutant des apostrophes avant les nombres ou ajoutés automatiquement lors de l'importation de données, le moyen le plus simple de les convertir en nombres consiste à utiliser l'option de vérification des erreurs. Voici comment procéder :
Vous pouvez utiliser cette méthode si vos nombres sont alignés à gauche et que vos cellules ont un petit triangle vert (un indicateur d'erreur) dans le coin supérieur gauche des cellules. Cela signifie que les nombres sont formatés sous forme de texte.
Sélectionnez ces cellules et vous verrez un avertissement jaune en haut à droite de la sélection. Ensuite, cliquez sur l'option « Convertir en numéro » dans la liste déroulante.
Vos zéros seront supprimés et les nombres seront reconvertis au format numérique (aligné à droite).
Suppression des zéros non significatifs en Multiplier/Diviser par 1
Une autre façon simple et la meilleure de supprimer l'interlignage consiste à multiplier ou à diviser les nombres par 1. La division ou la multiplication de la valeur ne change pas la valeur, elle convertit simplement la valeur en nombre et supprime les zéros non significatifs.
Pour ce faire, tapez la formule de l'exemple ci-dessous dans une cellule et appuyez sur ENTRÉE. Les zéros non significatifs seront supprimés et la chaîne sera reconvertie en nombre.
Ensuite, appliquez cette formule à d'autres cellules à l'aide de la poignée de remplissage.
Vous pouvez obtenir les mêmes résultats en utilisant la commande « Coller spécial ». Voici comment:
Tapez la valeur numérique « 1 » dans une cellule (disons en B2) et copiez cette valeur.
Ensuite, sélectionnez les cellules dans lesquelles vous souhaitez supprimer les zéros non significatifs. Ensuite, faites un clic droit sur la sélection, puis sélectionnez l'option "Collage spécial".
Dans la boîte de dialogue Collage spécial, sous Opération, choisissez l'option « Multiplier » ou « Diviser » et cliquez sur « OK ».
C'est tout, vos zéros non significatifs seront supprimés, laissant les chaînes sous forme de nombres.
Supprimer les zéros non significatifs à l'aide d'une formule
Un autre moyen simple de supprimer les zéros préfixés consiste à utiliser la fonction VALUE. Cette méthode peut être utile si vos zéros non significatifs ont été ajoutés à l'aide d'une autre formule ou apostrophe ou par une mise en forme personnalisée.
=VALEUR(A1)L'argument de la formule peut être une valeur ou la référence de cellule qui a la valeur. La formule supprime les zéros non significatifs et convertit la valeur du texte en nombre. Ensuite, appliquez la formule au reste des cellules.
Parfois, vous souhaiterez peut-être supprimer les zéros non significatifs mais conserver les nombres au format texte. Dans de tels cas, vous devez utiliser les fonctions TEXT() et VALUE() ensemble comme ceci :
=TEXTE(VALEUR(A1),"#")La fonction VALUE convertit la valeur de A1 en nombre. Mais le deuxième argument, '#' reconvertit la valeur au format texte sans aucun zéro supplémentaire. En conséquence, vous obtiendriez les nombres sans zéros non significatifs mais toujours au format texte (aligné à gauche).
Supprimer les zéros non significatifs à l'aide de la fonctionnalité Texte en colonnes d'Excel
Une autre façon de supprimer les zéros non significatifs consiste à utiliser la fonction Text to Columns d'Excel.
Sélectionnez la plage de cellules dont les nombres commencent par des zéros.
Ensuite, allez dans l'onglet « Données » et cliquez sur le bouton « Texte dans les colonnes » dans le groupe Outils de données.
L'assistant « Convertir le texte en colonnes » apparaîtra. À l'étape 1 sur 3, sélectionnez « Délimité » et cliquez sur « Suivant ».
À l'étape 2 sur 3, décochez tous les délimiteurs et cliquez sur « Suivant ».
À la dernière étape, laissez l'option Format de données de la colonne sur « Général » et choisissez la destination (la première cellule de la plage) où vous voulez vos nombres sans zéros non significatifs. Ensuite, cliquez sur « Terminer »
Et vous obtiendrez les nombres sans interligne dans une colonne séparée, comme indiqué ci-dessous.
C'est tout.