Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : transformer un nombre en chiffres ou en nombre en lettres

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 […]

personnes ont consulté cet article

2 minutes

Rédigé par Thierry Vanoffe - il y a 2 ans

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).

Chiffres Google Sheets

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 similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Thierry Vanoffe

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.

S’abonner
Notification pour
6 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Patrick TELLIER

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;)))))))

Patrick TELLIER

Lire A191;A181 (erreur du copier/coller)

Patrick TELLIER

A1 91 ;A1 81

Patrick TELLIER

Impossible à écrire A1 « différent » 91 ; A1 « différent » 81
différent =

Stéphanie

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é.