Comment supprimer les zéros non significatifs dans Excel [5 façons simples]

Beaucoup de gens ont une relation amour-haine avec les zéros non significatifs dans Excel.Parfois tu le veux, parfois non.

Bien qu'Excel soit programmé pour supprimer automatiquement tous les zéros non significatifs des nombres, il existe certaines situations dans lesquelles vous pouvez rencontrer ces situations.

Dans ce tutoriel Excel, je vais vous montrerCommentSupprimez les zéros non significatifs des nombres dans Excel.

Alors, commençons!

Causes possibles des zéros non significatifs dans Excel

Comme je l'ai mentionné, Excel supprime automatiquement tous les zéros non significatifs des nombres.Par exemple, si vous entrez 00100 dans une cellule d'Excel, il le convertira automatiquement en 100.

Dans la plupart des cas, cela a du sens, car ces zéros non significatifs n'ont pas vraiment de sens.

Mais dans certains cas, vous pourriez en avoir besoin.

Voici quelques raisons pour lesquelles vos nombres peuvent conserver des zéros non significatifs :

• Si le nombre a été formaté en tant que texte (principalement en ajoutant une apostrophe avant le nombre), il conservera les zéros non significatifs.
• Les cellules peuvent avoir été formatées pour toujours afficher des nombres d'une certaine longueur.Si le nombre est plus petit, des zéros non significatifs sont ajoutés pour le compenser.Par exemple, vous pouvez formater une cellule pour qu'elle affiche toujours 5 chiffres (si le nombre est inférieur à 5 chiffres, des zéros non significatifs sont automatiquement ajoutés)

La méthode que nous choisissons pour supprimer les zéros non significatifs dépend de ce qui en est la cause.

La première étape consiste donc à identifier la cause afin que nous puissions choisir la bonne façon de supprimer ces zéros non significatifs.

Comment supprimer les zéros non significatifs des nombres

Vous pouvez supprimer les zéros non significatifs des nombres de plusieurs manières.

Dans cette section, je vais vous montrer cinq de ces méthodes.

Convertir du texte en nombres avec des options de vérification des erreurs

Si la raison des premiers nombres est que quelqu'un a ajouté une apostrophe devant eux (convertissant ces nombres en texte), vous pouvez utiliser la vérification des erreurs pour reconvertir ces nombres en nombres en un seul clic.

C'est probablement le moyen le plus simple de se débarrasser des zéros non significatifs.

Ici, j'ai un ensemble de données avec des nombres avec une apostrophe avant ces nombres et des zéros non significatifs.C'est pourquoi vous voyez que ces nombres sont alignés à gauche (alors que les nombres par défaut sont alignés à droite) et ont également des 0 en tête.

Voici les étapes pour supprimer ces zéros non significatifs de ces nombres :

1. Sélectionnez le nombre dont vous souhaitez supprimer les zéros non significatifs.Vous remarquerez une icône jaune dans le coin supérieur droit de la sélection.
2. Cliquez sur l'icône de vérification de bogue jaune
3. Cliquez sur "Convertir en nombre"

C'est tout!Les étapes ci-dessus permettrontsupprimer l'apostropheet reconvertissez ces valeurs de texte en nombres.

Et comme Excel est programmé pour supprimer par défaut les espaces de tête de n'importe quel nombre, vous verrez que cela supprime automatiquement tous les zéros de tête.

Remarque : Cette méthode ne fonctionnera pas si des zéros non significatifs ont été ajoutés dans le cadre du format numérique personnalisé de la cellule.Pour gérer ces situations, utilisez les méthodes décrites ci-après.

Modifier le format numérique personnalisé d'une cellule

Une autre raison très courante pour laquelle vos nombres peuvent afficher des zéros non significatifs est lorsque vos cellules ont été formatées pour toujours afficher un certain nombre de nombres dans chaque nombre.

Beaucoup de gens veulent que les nombres aient l'air cohérents et de la même longueur, ils spécifient donc une longueur minimale pour les nombres en modifiant le format des cellules.

Par exemple, si vous souhaitez que tous les nombres apparaissent sous la forme de 5 chiffres, si vous avez un nombre à seulement trois chiffres, Excel y ajoutera automatiquement deux zéros non significatifs.

Ci-dessous, j'ai un ensemble de données avec un format de nombre personnalisé appliqué pour toujours afficher au moins cinq chiffres dans une cellule.

La façon de se débarrasser de ces zéros non significatifs consiste simplement à supprimer le formatage existant de la cellule.

Voici les étapes à suivre :

1. Sélectionnez des cellules avec des zéros non significatifs
2. Cliquez sur l'onglet Accueil
3. Dans le groupe Nombres, cliquez sur le menu déroulant Format de nombre
4. Sélectionnez "Général"

Les étapes ci-dessus modifieront le format de nombre personnalisé de la cellule et maintenant les nombres apparaîtront comme prévu (sans zéros en tête).

Notez que cette technique ne fonctionne que si la raison du zéro non significatif est une mise en forme numérique personnalisée.Cela ne fonctionnera pas si vous utilisez des apostrophes pour convertir des nombres en texte (dans ce cas, vous devez utiliser la méthode précédente)

Multiplier par 1 (en utilisant une technique de pâte spéciale)

Cette technique fonctionne dans les deux cas (lorsque le nombre a été converti en texte à l'aide d'apostrophes ou lorsqu'un format de nombre personnalisé a été appliqué à la cellule).

Supposons que vous disposiez d'un ensemble de données comme ci-dessous et que vous souhaitiez en supprimer les zéros non significatifs.

Voici les étapes pour le faire

1. Copiez toutes les cellules vides dans la feuille de calcul
2. Sélectionnez les cellules contenant des nombres dont vous souhaitez supprimer les zéros non significatifs
3. Cliquez avec le bouton droit sur la sélection et cliquez sur Collage spécial.Cela ouvrira la boîte de dialogue Collage spécial
4. Cliquez sur l'option Ajouter (dans le groupe Actions)
5. Cliquez sur OK

Les étapes ci-dessus ajoutent 0 à la plage de cellules sélectionnée et suppriment tous les zéros et apostrophes non significatifs.

Bien que cela ne modifie pas la valeur de la cellule, il convertit toutes les valeurs de texte en nombres et copie la mise en forme de la cellule vide que vous avez copiée (remplaçant ainsi la mise en forme existante qui fait apparaître les zéros non significatifs).

Cette méthode n'affecte que les nombres.S'il y a une chaîne de texte dans la cellule, elle restera inchangée.

Utiliser la fonction VALEUR

Un autre moyen rapide et facile de supprimer les zéros non significatifs consiste à utiliser une fonction de valeur.

Cette fonction prend un argument (qui peut être du texte ou une référence de cellule avec du texte) et renvoie une valeur numérique.

Cela s'applique également aux deux cas où le premier numéro est une apostrophe (utilisée pour convertir des nombres en texte) ou le résultat d'un format numérique personnalisé.

Supposons que j'ai un ensemble de données qui ressemble à ceci :

Voici la formule pour supprimer les zéros non significatifs :

= VALEUR (A1)

Remarque : Si vous voyez toujours des zéros non significatifs, vous devez accéder à l'onglet Accueil et modifier le format de cellule en Général (dans le menu déroulant Format de nombre).

Utiliser le texte dans les colonnes

Bien que la fonctionnalité Texte en colonnes soit utilisée pour diviser les cellules en plusieurs colonnes, vous pouvez également l'utiliser pour supprimer les zéros non significatifs.

Supposons que vous ayez un ensemble de données qui ressemble à ceci :

Voici les étapes pour supprimer les zéros non significatifs à l'aide de Text to Columns :

1. Sélectionnez une plage de cellules avec des nombres
2. Cliquez sur l'onglet Données
3. Dans le groupe Outils de données, cliquez sur Texte aux colonnes
4. Dans l'assistant Convertir le texte en colonnes, apportez les modifications suivantes :
   1. Étape 1 sur 3 : Sélectionnez "Séparer" et cliquez sur "Suivant"
   2. Étape 2 sur 3 : Désélectionnez tous les délimiteurs et cliquez sur Suivant
   3. Étape 3 sur 3 : Sélectionnez une cellule cible (B2 dans ce cas) et cliquez sur Terminer

Les étapes ci-dessus doivent supprimer tous les zéros non significatifs et ne fournir que des chiffres.Si vous voyez toujours des zéros non significatifs, vous devez modifier le format de la cellule en Général (cela peut être fait à partir de l'onglet Accueil)

Comment supprimer les zéros non significatifs du texte

Bien que toutes les méthodes ci-dessus fonctionnent bien, ces méthodes ne fonctionnent que pour les cellules qui ont des valeurs numériques.

Mais que se passe-t-il si vos valeurs alphanumériques ou textuelles comportent également des zéros non significatifs.

Dans ce cas, la méthode ci-dessus ne fonctionnera pas, mais grâce aux formules étonnantes d'Excel, vous pouvez toujours obtenir le temps.

Supposons que vous disposiez d'un ensemble de données comme celui-ci et que vous souhaitiez en supprimer tous les zéros non significatifs :

Voici la formule pour le faire :

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Laissez-moi vous expliquer comment cette formule fonctionne

La partie REMPLACEMENT de la formule remplace les zéros par des blancs.Donc pour la valeur 001AN76, la formule de remplacement donne le résultat 1AN76

La formule LEFT extrait ensuite le caractère le plus à gauche de cette chaîne résultante, qui dans ce cas est 1.

La formule FIND trouve alors le caractère le plus à gauche donné par la formule LEFT et renvoie sa position.Dans notre exemple, pour la valeur 001AN76, cela donnerait 3 (c'est-à-dire la position de 1 dans la chaîne de texte d'origine).

1 est ajouté au résultat de la formule FIND pour s'assurer que nous avons extrait toute la chaîne de texte (à l'exception des zéros non significatifs)

Soustrayez ensuite le résultat de la formule FIND du résultat de la formule LEN, qui donne la longueur de la chaîne de texte entière.Cela nous donne la longueur de la boucle de texte sans les zéros non significatifs.

Cette valeur est ensuite utilisée avec la fonction DROITE pour extraire toute la chaîne de texte (à l'exception des zéros non significatifs).

Si vos cellules peuvent avoir des espaces de début ou de fin, il est préférable d'utiliser la fonction TRIM pour chaque référence de cellule.

Par conséquent, la nouvelle formule avec l'ajout de la fonction TRIM ressemble à ceci :

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)

Par conséquent, vous pouvez utiliser ces méthodes simples pour supprimer les zéros non significatifs des jeux de données dans Excel.

J'espère que vous avez trouvé ce tutoriel utile!