Excel- Transformer rapidement le format texte en format chiffre.
Vous avez ouvert ou importé un document en format texte (extension .txt) dans Excel ou encore, pour une raison quelconque, la colonne a été formatée en texte et vous essayez d’effectuer des formules mathématiques, mais le résultat vous donne #valeur!
Remarque importante:
Ce truc ne fonctionnera pas si vos chiffres contiennent un point [ . ] au lieu d’une virgule [ , ]. Pour corriger le problème, faites une recherche et un remplacement (CTRL+H); cherchez le [.] et remplacez-le par [,] et le tour est joué. Vous n’aurez probablement pas à faire la procédure ci-dessous.
Premièrement, comment savoir si le chiffre est en format texte ou nombre.
Emplacement des données dans la cellule.
Par défaut, Excel place les nombres et les dates à droite, et le texte à gauche.
Le résultat de votre formule mathématique vous donnera :
- Excel 2003 : Un message d’erreur #valeur.
- Excel 2007 : Le résultat dans la cellule. Par contre, aucun résultat ne s’affichera dans la barre d’état (dans le bas de votre écran); vous devez quand même les convertir en nombre pour éviter les problèmes lors des tris et des filtres.
Remarque:
Si vous devez faire des opérations mathématiques avec les nombres d’une colonne, vous devez placer les nombres dans une colonne et les lettres dans une autre.
Procédure pour convertir en nombres les chiffres qui sont en format texte
Même si vous sélectionnez votre colonne et que vous lui donnez un des formats pour les nombres, les chiffres resteront en format texte.
Si vous retapez vos chiffres, cela va vous prendre un temps fou et le risque d’erreurs (inversion de chiffres) augmente. Cette méthode est rapide et infaillible!
Pour la conversion, suivez ces étapes:
J’utiliserai l’exemple suivant pour la démonstration. Les chiffres sont placés à gauche, donc c’est du texte!
- Tapez le chiffre 1 dans la colonne B1
- Copiez la cellule B1
Méthode pour copier:
Clic à droite sur la souris et sélectionnez Copier;
CTRL+C sur votre clavier;
Icône Copier
- Sélectionnez la portion à modifier. Dans notre exemple, la cellule A1 à A12.
- Cliquez sur la souris à droite ou sur la flèche de l’icône Coller, et sélectionnez Collage spécial
- Choisissez Multiplication
- Cliquez sur OK
- Cliquez dans une autre cellule ou sur la touche Esc pour annuler la fonction de coller.
- Voici le résultat. Sur certains ordinateurs (selon les paramètres), vous aurez le format nombre (1,00) et pour d’autres, vous aurez le format standard. (1).
- Supprimez la cellule B1, elle n’est plus utile.
C’est tout! Vous êtes prêt à faire vos formules ou vos opérations mathématiques.
Je vous entends dire, « Mon dieu que j’ai perdu du temps avec ça! ». Dites-vous que moi aussi j’en ai perdu jusqu’à ce que je trouve cette astuce!
Différents formats pour les nombres et leurs caractéristiques:
- Standard (format par défaut)
- Nombre (2 décimales)
- Monétaire (2 décimales et signe $)
- Comptabilité (2 décimales, une espace et le signe de $)
Vous voulez économiser l’encre et le nombre de feuilles utilisées! Utilisez le format Nombre. Le signe de $ n’étant pas nécessaire dans 99% du temps, vous pourrez réduire la largeur de vos colonnes et la quantité d’encre utilisée. C’est un petit détail qui à la fin de l’année fait la différence sur les achats de cartouches et de feuilles ! (Dans mon ancienne vie, j’étais contrôleur en comptabilité et ça refait surface!)
Bon travail et bonne semaine!
Hélène
Tags: chiffres qui ne s'additionnent pas, format texte
Cette entrée a été publiée le lundi 20 septembre 2010 à 5 h 30 min, et rangée dans Excel. Vous pouvez suivre les réponses à cette entrée via son flux RSS 2.0. Vous pouvez laisser un commentaire, ou faire un rétrolien depuis votre site.
Bonjour,
Je viens de lire vos explications pour changer du texte en nombre et je n’y arrive toujours pas. En fait mon cas est un peu plus complexe… Je me retrouve sur l’onglet 1 colonne A avec des données du style « 109 boucherie frais emballé ». j’ai donc extrait le 109 dans l’onglet 2 colonne A en mettant =stxt(feuil1!A1;1;3) donc mes résultats sont justes je ne récupère que le 109 mais, parce qu’il y a un mais, mes cellules en colonne A de l’onglet 2 restent en format texte je ne trouve pas de solution en avez-vous une à me proposer ? Je peux vous envoyer un extrait du fichier par mail si vous le souhaitez car pour tout vous dire je suis en stage dans une enseigne qui a mis en place des tableaux de bord de gestion qe je dois rendre plus simple à élaborer chaque semaine. Le soucis en plus étant que le numéro 109 cité plus haut correspond à une famille de produit et les familles de produit qui n’ont pas eu de vente durant la semaine ne s’affichent pas dans le tableau d’où la difficulté de la chose car je fais mes formules sur la semaine 22 mais est-ce que pour la semaine 23 elle seront toujours valables, n’y aura t-il pas de décalage si une famille se rajoute ? Enfin bref comme vous le voyez ce problème me prend littéralement la tête cela fait 1 semaine que je recherche des solutions.
Merci d’avance !
HUBERT Sonia
Bonjour Hubert Sonia,
J’ai le plaisir de répondre aux questions de gens quand la solution est rapide à régler et à expliquer.
Dans votre courriel, il existe plusieurs notions: par exemple, le fait de faire une formule pour sortir du texte d’une chaine de caractère n’en fait qu’une formule et non du texte ni des chiffres. Donc on ne peut pas le convertir. Il existe des passe-passe pour y arriver, mais cela varie selon les besoins du programme.
Pour pouvoir vous aider, j’ai besoin de voir le document et de m’y pencher pour trouver les meilleures solutions avec vous.
Je suis travailleuse autonome et mon travail est de former et de dépanner les personnes ou les entreprises. J’utilise GoToMeeting pour voir votre document et en même temps je vous explique par téléphone ou par Skype comment le faire.
Le programme GoToMeeting est un programme sécurisé et je ne vois que votre écran, je ne peux pas entrer dans votre ordinateur.
Si le service vous intéresse, envoyez-moi vos coordonnées et je vous appelle pour prévoir un rendez-vous pour un GoToMeeting.
Hélène Marchand
________________________________________
Merci pour l’information! C’était une info que j’avais vraiment besoin sans passer par VBA!
Merci du coup de main (ou d’excel 😉 ) !
William
Les trucs et astuces sont là pour ça. Si vous en connaissez et que vous voulez les partager, c’est le bon blog.
Merci d’avoir pris le temps de m’écrire
Hélene
Bonjour,
Merci pour l’astuce. J’ai reproduit la démarche de convertir les nombres au format texte en nombre grâce à la fonciton copier-additionné. Le seul hic est que dans les cases vides de mon tableau j’ai du coup des « 0 » ce qui rend le tableau peu lisible. Y-a-t-il un moyen d’éviter cela ? (tableau de 33 802 celules).
Bonjour Dominique,
Je ne connais pas la version d’Excel que vous utilisez, mais si c’est 2007 utilisez le bouton Office, si c’est la version 2010, utilisez l’onglet fichier, ensuite Options- Options avancées – Afficher les options pour cette feuille de calcul – et décochez « Afficher un zéro dans les cellules qui ont une valeur nulle« .
Ceci devrait régler votre problème.
Bonne chance
Hélène Marchand
Bonjour,
Avec excel 2007, un triangle vert apparait dans le coin supérieur gauche de chaque cellule contenant un nombre au format texte. Il suffit de selectionner TOUTES les cellules à convertir.L’icone contenant un point d’exclamation à côté des cellules propose l’option « convertir en nombre »… Voilà environ 400 valeurs converties en 2 secondes 🙂
@+
Bonjour Pascalow,
Oui c’est vrai, la version 2007 et 2010 ont amélioré ce point fastidieux.
Merci beaucoup d’avoir pris le temps de partager votre connaissance avec les blogueurs de mon site.
Hélène Marchand
bonjour;
Je viens de changer mon ordi, j’ai installé mon logiciel simple comptable 2010 avec tous mes donnés.
Je ne réussis pas à mettre la monnaie. à chaque fois SC, me l’arrondi.
J’ai été dans configuration, paramètre, devise, décimale 2, séparateur de milliers(, la virgule), séparateur de décimale (le point .)
aucune idée, tout allait bien avant ça. et SC n’aide plus le pro 2010.
je ne sais pas comment réglé la problématique.
merci de ton aide
Ton problème vient du logiciel ICloud que tu dois avoir installer dans ton ordinateur. J’ai un gentil Monsieur qui m’a dit au travers du blogue qu’ICloud causait le problème et les 5 – 6 blogueurs qui avaient le même problème avaient tous ICloud. Ils l’ont enlevé et le problème c’est réglé. Il n’est pas compatible avec Simple comptable.
Hélène
Hello Hélène,
j’ai toute une colonne où mon client a entré des heures en format texte 🙁
J’ai essayé votre méthode (au cas où!) et bien sûr ça ne fonctionne pas (enfin, ça me donne des « 1 » partout). Est-ce qu’il y a un remède à ça ou faut-il vraiment que je retape tout au risque d’insérer des erreurs?
merci pour toute aide !
Bonne journée
Cécile
Bonjour Cécile,
Suite à ta demande, j’ai placé 0:00 dans une cellule et je l’ai copiée et ensuite j’ai choisi la colonne formatée en texte et j’ai fait un collage spécial addition et cela a converti le texte en heure.
Bonne chance
Hélène Marchand
Bonjour Madame.
J’aimerais savoir comment faire pour convertir ces données en texte, en nombres. Cela fait 2 semaines que je travail dessus et j’arrive à rien. Je vous fais parvenir des grilles de tirages de loto pour voir qu’est ce qui ne va pas. Car il faut que j’addition les chiffres de chaque grille, pour avoir le poids brut et aussi pour filtrer, trier et recouper pour environ 89 millions de grilles.
Merci à l’avance
Gilles
C 1 : 01 02 04 13 22 23 25
C 2 : 01 02 04 13 22 23 26
C 3 : 01 02 04 13 22 23 27
C 4 : 01 02 04 13 22 23 28
Bonjour Gilles,
En partant, si tu as 01, Excel le voit comme du texte. J’ai chercher un format avec un 0 dans les chiffres et je n’ai rien vu. Si quelqu’un a une bonne idée pour Gilles, faites-nous la connaître.
Hélène Marchand
Merci Helene, je vais attendre avec impatience une réponse car je me vois mal refaire 89 millions de grilles manuellement ouff . Mon projet de faire un méga groupe va aller à poubelle meme si je sais que mon système est valable.
En attente d’une réponse miracle ( rire)
Gilles
Re Madame Hélene . J’ai trouvé une solution très simple .Aucune formule de programmation rien pant toute (lol). Dans excel menu, données, convertir et le tour est joué. C’était simple hein (lol)Me suis cassé la tête depuis 2 semaines à lire des forums sur internet et j ai trouvé la simplicité même, mais il y avait rien la.
Au plaisir de se reparler
Gilles
a+
Bonjour Gilles,
On oublie souvent les fonctions Excel, elles sont en effet d’une grande aide.
Hélène Marchand
Pour récupérer 109 dans « 109 boucherie », on peut procéder par étapes, en ajoutant des colonnes.
A) on insère une colonne avant celle qui contient « 109 boucherie »
B) on place une formule dans cette nouvelle colonne, qui extrait « 109 », telle que : « =stxt(feuil1!A1;1;3) »
C) on insère une nouvelle colonne devant cette colonne avec formule
D) on sélectionne (toute la colonne par exemple), celle qui contient le résultat (avec formule) : « 109 », qu’on « Copie »
E) dans la dernière colonne qu’on vient d’insérer, on va faire un « Collage spécial / Collage par valeurs »
Et le tour est joué.
On peut supprimer la première colonne qu’on a insérée, celle qui contient une formule
(la conversion des données « en place » était possible jusqu’à Excel4, puis elle avait disparu, avec une gestion imposée, soi-disant « intelligente » ; la conversion des données « en place » semble revenir avec les dernières versions d’Excel, ce qui est une bonne chose (;-))
Merci pour le transfert d’information. Ces informations seront utiles aux blogueurs.
Hélène Marchand
bonjour je voulais placer les chiffres à gauche sans rajouter de point à la fin . par exemple 69003
Bonjour YaYa,
Il faut utiliser l’onglet Accueil – dans le groupe Alignement et il faut utiliser celui qui dit alignement à gauche
Hélène
Bonjour,
Voilà je vous explique, je crée un document excel style facture, dans un cellule j ai créé préalablement un menu déroulant avec les références des articles, le problème c est qu’il y a des références purement de nombres et d autres qui se présentent comme cela « b12345 »,dans la deuxième colonne de mon tableau, j ai la description des articles qui s affiche, pour ce qui est des articles de pure nombre, cela s affiche mais pour ceux avec « b12345 » il est inscrit #N/A… Comment puis-je faire pour transformer cette cellule texte en nombre?
Merci d’avance de votre aide.
Lizou
Bonjour Lizou,
Ton tableau est bien fait, et une cellule b12345, ne peut être que du texte. Quand une donnée contient des lettres et des chiffres, elle est formaté en texte automatiquement. Le problème vient de ton tableau contenant les valeurs recherchées. La colonne qui indique #N/A est une colonne formatée en nombre, donc la solution c’est de la formater en texte et tu vas régler ton problème. J’ai eu le problème très souvent et la solution a toujours été le format de mes cellules dans le tableau de mes valeurs recherchées.
Bonne chance
Hélène Marchand
j’utilise Sage 50 Comptabilité Supérieur (Lancement 2014.2) et lorsque j’ouvre ou importe un rapport dans Excel (2007), les formules et les nombres sont en format texte au lieu d’une valeur calculée.
Quel est la solution s.v.p. ?
Merci !
Bonjour Roberto,
Sage 50 dit de regarder dans les configurations – préférences de l’utilisateur – et cocher Langue Microsoft Excel – si ton Office est en français choisit français, sinon anglais.
Je l’ai essayé chez moi et le résultat a été négatif, donc voici donc ce que je fais pour pallier au problème.
Je sélectionne la première cellule qui contient un chiffre (format texte) et toutes les autres cellules qui en contiennent.
Un petit carré vient se positionner à la gauche de la première cellule sélectionnée, si tu cliques dedans (le petit carré), il te demande ce que tu veux faire, tu réponds Convertir en nombre. Et là par miracle tous les chiffres formats textes deviennent en format chiffre.
Bonne chance
Hélène Marchand
Bonjour,
J’utilise Excel 2010.
Dans mes colonnes de calculs, le texte (TR) est souvent utilisé.
Ex: A1: TR A2: 10 A3: 0 A4: 5 A5: TR
Lorsque je veux faire la somme de cette colonne, il ne tient pas compte du (TR) et c’est bien ainsi car le compte est bon = 15.
Par contre, le problème subvient lorsque je veux en faire la moyenne.
Il ne tient pas compte des colonnes avec le TR et divise le tout par 3, ce qui me donne une moyenne de 5.
Je voudrais que mes TR aient une valeur de 0 et qu’il soient pris en considération dans la moyenne de la colonne.
Ainsi dans mon exemple, en convertissant TR en 0 ma moyenne serait de 3.
Est-ce possible de convertir mes TR en 0 dans le calcul de la moyenne?
Merci infiniment.
Suite à mon dernier message, j’ai oublié d’inclure un détail important.
Je reformule ma question, est-ce possible de convertir mes TR en la valeur 0 tout en conservant sa forme textuelle?
Je voudrais continuer à voir les TR dans ma colonne.
Merci.
Bonjour Patrick,
Quand un cellule est en format texte (TR) c’est impossible de faire un calcul avec. Par contre, tu peux insérer une autre colonne à côté et utilisez une formule Si par exemple =Si la cellule A1= »TR » ; 0 ; A1. et copier cette formule dans toute la colonne. Tu pourrais faire tes calculs à partir de cette deuxième colonne. C’est faire un peu de gymnastique, mais ça marche. Tu peux aussi cacher cette colonne, si tu ne veux pas la voir.
Bonne chance
Hélène Marchand
Bonjour,
Je cherchais en vain pourquoi je n’arrivais pas à sortir un graphe avec mon tableur !
Grace à vous il a suffi que je change le point en virgule et j’ai enfin obtenu ma coube.
Mille mercis
Amitiés
Mic
Bonjour Hélène,
je travaille sur excel 2011 pour Mac.
Je souhaite convertir des cellules importées en texte, en nombre. Comme cela est très bien expliqué dans votre blog……. Rien à faire, rien ne bouge.
Je suis désespéré car j’ai essayé plein de méthode. Aucune ne marche. Je veux bien vous envoyer mon fichier excel par mail (il s’agit d’un fichier d’essai) pour que vous voyez ce qu’il est possible de faire.
Merci de votre réponse, voire par mail si c’est plus simple.
Bonjour Grigou,
Je ne connais pas la version Excel Mac, petite question comme ça, est-ce qu’il y a un triangle vert dans le coins supérieur gauche de la cellule ou des cellules. Si oui, tu peux sélectionner la première cellule jusqu’à la dernière et tu devrais voir un carré à la gauche de ta sélection, si tu cliques dessus, tu devrais voir convertir en chiffre. Je peux regarder ton fichier, mais à ce moment-là il y a des frais qui s’y rattache. Tu peux communiquer avec moi par courriel si le service t’intéresse.
Bonne chance
Hélène Marchand
Bonjour Hélène,
Non, il n’y a pas de triangle, ni vert, ni de toute autre couleur.
Peux-tu me donner tes tarifs si je t’envoie le fichier?….. et les chances de succés…
Merci
Grigou
Enorme la solution de multiplication par 1 pour convertir du texte en nombre!!!
« Comme j’ai perdu du temps avec ça!!! »
Merci Hélène!!!
bonjour,
votre solution marche mais j’ai du texte sous la forme :
4s, 5m (pour min) que je veux convertir en format heure, le résultat que j’obtiens affiche les secondes en heures comme suit : 4:00:00 alors qu’il me faut des secondes
Bonjour Sara,
Il te faudra faire plusieurs manipulations:
1-Sépare la colonne avec la méthode convertir et demandé « délimiter par une virgule »,
2-Lorsque les colonnes sont séparées, fait une recherche et remplacement pour enlever les S et M.
3-Fait la fonction Concaténer, (fonction Excel – contraire de convertir) les colonnes, exemple:
=CONCATENER(A2; »: »;B2; »: »;C2). Les séparateurs : sont important et les guillemets aussi.
4-Il te reste à modifier le format des heures avec format spécial et tu vas chercher celui ou l’heure est entre crochets – [h]:mm:ss, c’est important de changer de format si tu veux faire des calculs avec les dates, sinon le calcul ne sera pas bon.
Bonne chance
Hélène Marchand
Bonjour Hélène,
Merci beaucoup pour votre site. C’est une mine d’or pour nous tous;
J’ai un client qui m’a donné un fichier avec des dates présentée comme ceci.
15 11 1934
03 03 1925
20 12 1923
le fichier est très long et j’aurais juste besoin de les afficher comme cela pour l’intégrer dans une base de donnée.
15/11/1934
03/03/1925
20/12/1923
Avez vous une idée pour que ce ne soit pas trop laborieux ?
merci
Armel
Bonjour Armel,
Tu pourras dire que tu m’as fait chercher!
Si je comprends bien, le format 15 11 2015 est dans la même cellule. Il faut commencer par les séparer en 3 cellules
–Donnée
-Convertir
-tu choisis Délimité,
-le séparateur sera l’espace
-la destination sera un emplacement ou tu auras 3 cellules de vide pour ne pas écraser le texte des autres colonnes.
Cela va diviser ta cellule en trois.
Ensuite, il faut les créer la cellule pour la date :
-Formule
-Date
-Tu sélectionnes les cellules pour remplir la formule,
-Ensuite tu copies cette formule jusqu’en bas de tes données.
-Après, tu copies cette colonne et dans la colonne suivante tu utilises –collage spécial – valeur pour avoir le résultat et non la formule. Ceci te permettre d’enlever toutes les colonnes inutiles et garder la date.
Bonne chance
Hélène Marchand
j’ai un tableau sur excel chaque cellule se constitue trois chiffres avec un espace entre ces chiffres et je veux faire dans l’espace un signe comme : ° ‘ »
comment je peux faire l’opération si possible.
et merci.
Bonjour Khadija,
Pour faire cette transformation, tu dois utiliser 2 formules: la première dans l’onglet Données, Convertir, ce qui mettra chaque chiffre dans une colonne. Ensuite, utilise la formule Concaténer pour réunir les chiffres divisés en 3 colonnes et le symbole de ton choix pour les séparer. Le symbole doit être entre » « . Tu trouveras dans mes blogues comment faire ces deux formules.
Bonne chance
Hélène Marchand
bonjour,
j’ai un souci car j’ai un colonne de matricules (plusieurs centaines) avec des chiffres ou nombres comme tels :
5
25
123
14
et il me faudrait les transformer de cette manière
00000005
00000025
00000123
00000014
Il faut non seulement que l’affichage soit comme tel mais aussi que dans la zone de saisie de la cellule l’affichage se fasse comme çà .
J’ai déjà vu un fichier avec le bon format (sauf que toutes les cellules ont le petit triangle en haut à gauche) Mes cellules s’affichent comme étant en format texte .
je ne sais pas comment faire.
Ce fichier doit me servir à une importation et je suis bloquée.
Si je peux avoir de l’aide elle est la bienvenue.
Merci
Bonjour,
Je ne sais pas si tu peux réimporter tes données, mais en texte et non en nombre. C’est important que le format soit texte, sinon les 0 disparaissent. Je réfléchis encore à la formule qui ajouterait les 0 tout seul en fonction de 8 cases.
Je te reviens
Hélène Marchand
Bonjour,
J’ai Excel 2007 et j’ai essayé à plusieurs reprises la procédure que vous décrivez et cela ne fonctionne pas : mes nombres en format texte demeurent en format texte.
Bonjour Hervé,
Tu peux essayer de sélectionner les chiffres inscrits en texte et une petite boite va apparaître du côté gauche de ta sélection, clique sur la boite et choisit l’option convertir en chiffre.
Bonne chance
Hélène Marchand
Bonjour,
l’astuce de copier 1 avec le collage spécial multiplication à parfaitement fonctionné : MERCI
Bjour
un exemple
je fais un tableau
47193500001
56581942001
59706603001
48906265001
11583360000
48202670001
51275000001
59797903001
57881700001
58576265001
13901264000
62531065001
je voudrai passer de 11 a 6 premier nombre sans toutefois passer colonne par colonne y a t il pas une formule pour aboutir a un tel résultat
Salut,
Tu veux garder les chiffres de droite ou de gauche?. La formule sera différente. Regarde la fonction gauche et la fonction droite dans Formules – texte – gauche ou droite, selon tes besoins.
Hélène Marchand
Bonjour Hélène,
J’ai transféré un tableau du logiciel Acomba dans Excel et les chiffres sont en mode texte, j’ai tenté avec la méthode de multiplication et rien à faire ensuite avec la petite boîte qui pour moi est à droite et ça ne fonctionne pas non plus ???
salut
j’ai une feuille excel et je lorsque je fait la somme sur les colonnes il apparaît #VALEUR! je veux une solution
Merci
Bonjour Amel
C’est que tu as du texte dans ton calcul. Si les chiffres se placent à gauche, c’est que Excel les reconnait comme du texte. Et tu devrais voir aussi, un triangle vert dans le coin.
Hélène Marchand
Bonjour Sylvie,
Désolé pour le retard, as-tu essayer de changer les points par des virgules ou encore les virgules en points avec la fonction Rechercher-Remplace.
Hélène Marchand