Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Admin Workspace 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube Google Sheets : transformer un nombre en chiffres ou en nombre en lettres
Au détour d’une formation la semaine dernière. Jean-Yves me pose la question suivante : « Thierry, comment fais-tu pour convertir un nombre en lettres comme sur un chèque ? » Je réfléchis quelques instants, je scanne avec […]
Ce que vous allez découvrir
- Décomposer un nombre exprimé en chiffres pour le transformer en lettres
- Une formule qui permet de récupérer le nombre de dizaines et d'unités dans un nombre.
Google Sheets : transformer un nombre en chiffres ou en nombre en lettres
Au détour d’une formation la semaine dernière. Jean-Yves me pose la question suivante : « Thierry, comment fais-tu pour convertir un nombre en lettres comme sur un chèque ? » Je réfléchis quelques instants, je scanne avec mon œil bionique en quelques secondes les 471 formules de Google Sheets en français, je ne trouve pas. Je passe en anglais pour les 491, toujours rien (et oui il y en a 20 de plus si votre interface Google Sheets est en anglais, et cela fera l’objet d’un prochain article).
Une fois la formation terminée, un message dans le canal d’entraide SLACK des formateurs du réseau Numericoach (66 coachs, consultants, formateurs sur Google Sheets) et quelques secondes plus tard une réponse de notre Antoine Martin national avec cette formule :
=IFNA(RECHERCHEV(A1;D:E;2;)
;SI(A1<70; RECHERCHEV(CNUM(GAUCHE(A1;1))*10;D:E;2;)&SI(CNUM(DROITE(A1;1))=1;"-et";)&"-"&RECHERCHEV(CNUM(DROITE(A1;1));D:E;2;)
;SI(A1<80;"soixante"&
SI(CNUM(DROITE(A1;1))=1;"-et";)
&"-"&(RECHERCHEV(CNUM(DROITE(A1;1)+10);D:E;2;))
;SI(A1<90; RECHERCHEV(CNUM(GAUCHE(A1;1))*10;D:E;2;)&SI(CNUM(DROITE(A1;1))=1;"-et";)&"-"&RECHERCHEV(CNUM(DROITE(A1;1));D:E;2;)
;SI(A1<100;"quatre-vingt"&
SI(CNUM(DROITE(A1;1))=1;"-et";)
&"-"&(RECHERCHEV(CNUM(DROITE(A1;1)+10);D:E;2;)))))))Décomposer un nombre exprimé en chiffres pour le transformer en lettres
Un cachet d’aspirine plus tard, je me concentre quelques secondes pour décortiquer cette pépite composée de fonctions SI imbriquées, de fonctions de la famille texte comme GAUCHE, DROITE, CNUM et de recherches verticales saupoudrées d’esperluette (à ne pas confondre avec le piment d’Espelette, un peu moins fort que cette méga formule).
Tout d’abord, voyons de plus près la feuille de calcul Google Sheets créée pour l’occasion (merci à son génie-teur qui se reconnaîtra).
Remarquez la liste des nombres exprimés en chiffres associés à leurs expressions textuelles. Également, vous remarquerez que seuls les mots uniques sont présents. Les mots nombres absents sont en fait composés des autres mots déjà cités. Pour l’exemple la liste est dans la même feuille mais vous pourrez bien sûr la déplacer.
La formule a donc pour but de déterminer si le nombre en chiffres possède une correspondance directe, sinon, il faudra le recomposer avec les mots existants, en récupérant la dizaine et l’unité (pour les nombres inférieurs à 100).
C’est à ce moment que l’on remercie la langue française d’avoir conçu un système de lecture des nombres apparemment simple mais bourré d’exceptions !
Une formule qui permet de récupérer le nombre de dizaines et d’unités dans un nombre.
Prenons la première partie de la formule, ainsi :
=SIERREUR(RECHERCHEV(A1;D:E;2;)
Ce qui veut dire : s’il existe, affiche le mot-nombre de la valeur écrite en A1, qui correspond à la donnée écrite dans la deuxième colonne de la plage D:E correspondant au nombre de A1 présent dans la première colonne.
Prenons la suite :
SI(A1<70; RECHERCHEV(CNUM(GAUCHE(A1;1))*10;D:E;2;)&SI(CNUM(DROITE(A1;1))=1;"-et";)&"-"&RECHERCHEV(CNUM(DROITE(A1;1));D:E;2;)
Si la valeur en A1 est inférieure à 70, fais-moi une recherche verticale du premier caractère de la cellule A1, transforme-le en nombre et multiplie-le par 10 (3 devient trente), dans la plage D:E et prends la valeur de la deuxième colonne correspondant à ce nombre puis concatène-la (« & ») avec la valeur correspondante à la partie droite de la valeur limitée à un chiffre (soit le chiffre des unités).
=CNUM() convertit en nombre une chaîne, dans un format de date, d’heure ou de nombre reconnu par l’outil feuilles de calcul Google, indispensable dans cette formule pour effectuer une opération.
Ensuite la formule aborde les cas particuliers des nombres de la famille des 70 et des 90… Les Belges et Helvètes doivent bien se moquer de nous… 🙂
Il ne vous reste plus qu’à bidouiller pour construire la suite des formules pour les nombres supérieurs 100 dans ce fichier mis à votre disposition car la maison ne recule devant aucun sacrifice.
À se demander s’il ne serait pas plus simple de construire un référentiel unique de tous les mots nombres et de faire une simple recherche verticale !
Un grand merci à Antoine pour cette formule et à Jean-Yves de chez AirLiquide pour cette question très pertinente.
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets
- Articles connexes
- Plus de l'auteur
Sylvain TORCHOT
News
G Suite Essentials : les uilisateurs peuvent bénéficier de » AppSheet » gratuitement jusqu’en septembre
Cet article est périmé. L'abonnement Google Business Starter est toujours présent, voici le lien pour accéder à ses avantages. En avril 2020, Google a lancé trop discrètement, pendant le confinement, G ...
2 min
Thierry Vanoffe 4 ans
News
Gmail se met au bleu : découvrez les coches vérifiées pour une messagerie plus sûre !
Google a annoncé une nouvelle fonctionnalité de vérification de l'expéditeur pour Gmail. Dès maintenant, un badge de vérification, sous la forme d'une coche bleue, apparaîtra à côté du nom de ...
1 min
Thierry Vanoffe 2 ans
Thierry VANOFFE, consultant, formateur, coach Google Workspace CEO de Numericoach, leader de la formation Google Workspace en France. Passionné par Google, ce blog me permet de partager cette passion et distiller tutos, trucs, astuces, guides sur les outils Google. N'hésitez pas à me solliciter pour vos projets de formation.
Génial ! Bravo Antoine,
J’ai une amélioration à proposer : avant dernière ligne on teste si différent de 81 et 91 pour ne pas avoir Quatre-Vingt-et-un etc … ensuite j’attaque les centaines
=IFNA(RECHERCHEV(A1;D:E;2;)
;SI(A1<70; RECHERCHEV(CNUM(GAUCHE(A1;1))*10;D:E;2;)&SI(CNUM(DROITE(A1;1))=1;"-et";)&"-"&RECHERCHEV(CNUM(DROITE(A1;1));D:E;2;)
;SI(A1<80;"soixante"&
SI(CNUM(DROITE(A1;1))=1;"-et";)
&"-"&(RECHERCHEV(CNUM(DROITE(A1;1)+10);D:E;2;))
;SI(A1<90; RECHERCHEV(CNUM(GAUCHE(A1;1))*10;D:E;2;)&SI(CNUM(DROITE(A1;1))=1;"-et";)&"-"&RECHERCHEV(CNUM(DROITE(A1;1));D:E;2;)
;SI(A1<100;"quatre-vingt"&
SI(ET(CNUM(DROITE(A1;1))=1;A191;A181); »-et »;)
& »-« &(RECHERCHEV(CNUM(DROITE(A1;1)+10);D:E;2;)))))))
Lire A191;A181 (erreur du copier/coller)
A1 91 ;A1 81
Impossible à écrire A1 « différent » 91 ; A1 « différent » 81
différent =
Waouh! Quelle formule. Il n’y a qu’un expert qui puisse la dégoter ! Personnellement, sur mes documents, quand j’ai besoin de transformer un montant en lettres, j’utilise « MONEYTEXT » qui fonctionne ou plutôt qui fonctionnait très bien. Il suffisait de faire par exemple un =MONEYTEXT(E73; »EUR ») et ça indiquait en toutes lettres le chiffre en lettres suivi de la devise (euros ici). Mais depuis quelques temps, elle ne fait plus le job et indique le message d’erreur #NOM?. Je ne comprends pas pourquoi une fonction ne marche soudainement plus alors que le document n’a pas été modifié.
Je vais compléter la formule, merci