Expert Matricielles

Convertir un nombre en lettres

Temps de lecture 3 minutes

Convertir un nombre en lettres

Pour convertir un nombre en mots, la solution passait toujours par la création d'une macro VBA très complexe. Certains sites, comme celui-ci, vous propose un exemple de code à utiliser mais selon les règles grammaticales des pays, le code devait toujours être adapté ; ce qui n'est pas facile et source d'erreur.

Maintenant, avec Excel 365 et la nouvelle fonction LET, vous pouvez convertir un nombre en mots. La formule est très complexe et a nécessité beaucoup de temps pour son développement. Mais malgré cela, il y a certains points de la formule qui doivent être analysé avant de pouvoir être utilisée.

Convertir Nombre en Lettres

Caractéristique de la formule

Avant de recopier la formule complète et de l'appliquer à votre classeur, il est important d'analyser certains points qui pourraient être cause de disfonctionnement.

Principe des matrices dans Excel

Excel 365 est la seule version qui sache interpréter les plages matricielles dynamiques (c'est à dire retourner le résultat dans plusieurs cellules).

Une matrice de plusieurs valeurs s'écrit toujours entre accolages, mais une matrice peut s'écrire en ligne ou en colonne selon le type de séparateur que vous utilisez.

Par exemple, en configuration française,

  • le séparateur pour les colonnes c'est le point
  • le séparateur pour les ligne c'est le point-virgule
Matrice en configuration France

Mais si votre système est configuré dans un autre pays, il est fort possible que le séparateur en ligne ne soit pas le point mais l'anti-slach (\) ou simplement la virgule (,) comme c'est le cas en configuration US. Cette configuration est liée aux paramètres de votre ordinateur comme cela a été décrit dans cet article.

Séparateur de décimales

L'une des complexités de l'écriture de cette formule c'est qu'elle est capable d'interpréter les chiffres avec décimales et les chiffres sans.

L'astuce réside dans cette partie de la formule.

N; SUBSTITUE( TEXTE( A1; REPT(0;9)&",00" );",";"0")

Sans trop rentrer dans le détail de cette formule, ce qu'il faut constater c'est que nous avons 2 fois le symbole décimale virgule d'écrit

  • ,00
  • et le symbole de remplacement de la fonction SUBSTITUE à la fin ",";"0".

Si vous travaillez avec le séparateur de décimale point (.) il vous faudra remplacer ces 2 virgules par un point dans la fonction

N; SUBSTITUE( TEXTE( A1; REPT(0;9)&".00" );".";"0")

Ecrire Euros/Centimes ou rien

Telle que la formule est écrite, elle va toujours rajouter Euros à la fin d'un nombre et centimes si le nombre comporte des décimales.

Denom; {" million ";" mille ";" Euro ";" Centimes"}

Maintenant, si vous ne souhaitez pas afficher les mots Euros et Centimes, il faut simplement effacer ces mots MAIS EN CONCERVANT les guillemets vides pour respecter le nombre d'occurrences dans la matrice.

Denom; {" million ";" mille ";"";""}

Formule pour convertir un nombre en lettres (France)

La formule complète est la suivante

=LET(
Denom; {" million ";" mille ";" Euro ";" Centimes"};
Nums; {""."Un"."Deux"."Trois"."Quatre"."Cinq"."Six"."Sept"."Huit"."Neuf"};
Teens; {"Dix"."Onze"."Douze"."Treize"."Quatorze"."Quinze"."Seize"."Dix-sept"."Dix-huit"."Dix-neuf"};
Tens; {""."Dix"."Vingt"." Trente"." Quarante"."Cinquante"."Soixante"." Soixante"."Quatre-vingt"."Quatre-vingt"};
grp; {0;1;2;3};
LET(
N; SUBSTITUE( TEXTE( A1; REPT(0;9)&",00" );",";"0");
H; CNUM( STXT( N; 3grp+1; 1) ); T; CNUM( STXT( N; 3grp+2; 1) );
U; CNUM( STXT( N; 3*grp+3; 1) );
Htxt; SI( H; SI(H>1; INDEX( Nums; H+1 ) & " cent "; "cent " ); "");
Ttxt; SI( T>1; INDEX( Tens; T+1 ) & SI( U>0; "-"; "" ); " " );
Utxt; SI((T+U); SI((T=1)+(T=7)+(T=9); INDEX(Teens;U+1 );INDEX(Nums;U+1)));
SUPPRESPACE(SUBSTITUE(NOMPROPRE(CONCAT( SI( H+T+U; Htxt & Ttxt & Utxt & Denom; "" )));"Un Mille";"Mille"))
))

P.S : 3 Excel MVP et 6 jours de développement pour obtenir ce résultat 😉

Formule pour convertir un nombre en lettres (Belgique et Suisse)

Ces 2 pays n'utilisent pas soixante-dix et quatre-vingt-dix donc la formule doit être adaptée. Par contre, les suisses n'utilisent pas non plus le quatre-vingt mais huitante ou octante. Il suffit juste de corriger cette information dans la formule suivante dans la ligne Tens.

=LET(
Denom, {" million ";" mille ";" Euro ";" Centimes"},
Nums, {"","Un","Deux","Trois","Quatre","Cinq","Six","Sept","Huit","Neuf"},
Teens, {"Dix","Onze","Douze","Treize","Quatorze","Quinze","Seize","Dix-sept","Dix-huit","Dix-neuf"},
Tens, {"","Dix","Vingt"," Trente"," Quarante","Cinquante","Soixante"," Septante","Quatre-vingt","Nonante"},
grp, {0;1;2;3},
LET(
N, SUBSTITUTE( TEXT( A1, REPT(0,9)&",00" ),",","0"),
H, VALUE( MID( N, 3grp+1, 1) ), T, VALUE( MID( N, 3grp+2, 1) ),
U, VALUE( MID( N, 3*grp+3, 1) ),
Htxt, IF( H, IF(H>1, INDEX( Nums, H+1 ) & " cent ", "cent " ), ""),
Ttxt, IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
Utxt, IF((T+U), IF(T=1, INDEX(Teens,U+1 ),INDEX(Nums,U+1))),
TRIM(SUBSTITUTE(PROPER(CONCAT( IF( H+T+U, Htxt & Ttxt & Utxt & Denom, "" ))),"Un Mille","Mille"))
))

Related posts

Fonction matricielle SEQUENCE

Frédéric LE GUEN

FILTRE avec colonnes séparées

Frédéric LE GUEN

Filtre dynamique dans Excel

Frédéric LE GUEN

8 commentaires

Nicolas Georges 27/02/2021 at 11:44

Merci pour ce partage !

J'ai du lire tout l'article pour trouver que . devait s'écrire \ dans ma configuration ;-)
J'ai eu un soucis avec 3grp qu'il fallait écrire 3*grp.
Sinon, j'ai aussi un soucis avec Utxt : je dois ajouter un ;"") à la fin du premier IF sinon j'obtiens par exemple "Dix Mille Cent Faux Euros", moi je veux des vrais euros :-p

Nicolas.

PS: la formule FR finale avec ma version d'Excel 365 (version 2101 build 13628.20274) :
=LET(
Denom; {" million ";" mille ";" Euro ";" Centimes"};
Nums; {""\"Un"\"Deux"\"Trois"\"Quatre"\"Cinq"\"Six"\"Sept"\"Huit"\"Neuf"};
Teens; {"Dix"\"Onze"\"Douze"\"Treize"\"Quatorze"\"Quinze"\"Seize"\"Dix-sept"\"Dix-huit"\"Dix-neuf"};
Tens; {""\"Dix"\"Vingt"\" Trente"\" Quarante"\"Cinquante"\"Soixante"\" Soixante"\"Quatre-vingt"\"Quatre-vingt"};
grp; {0;1;2;3};
LET(
N; SUBSTITUE( TEXTE( $I$7; REPT(0;9)&",00" );",";"0");
H; CNUM( STXT( N; 3*grp+1; 1) ); T; CNUM( STXT( N; 3*grp+2; 1) );
U; CNUM( STXT( N; 3*grp+3; 1) );
Htxt; SI( H; SI(H>1; INDEX( Nums; H+1 ) & " cent "; "cent " ); "");
Ttxt; SI( T>1; INDEX( Tens; T+1 ) & SI( U>0; "-"; "" ); " " );
Utxt; SI((T+U); SI((T=1)+(T=7)+(T=9); INDEX(Teens;U+1 );INDEX(Nums;U+1));"");
SUPPRESPACE(SUBSTITUE(NOMPROPRE(CONCAT( SI( H+T+U; Htxt & Ttxt & Utxt & Denom; "" )));"Un Mille";"Mille"))
))

Répondre
Frédéric LE GUEN 27/02/2021 at 17:46

Il faut tout lire ;) Il y a toujours une raison derrière une formule.
Ok pour le 3*grp et merci

Répondre
Ephram 25/01/2021 at 15:41

Par contre pour modifier selon le pays, par exemple, la Suisse il faut juste remplacer "quatrevingt" par huitante et quatre vingt dix par nonante ou il faut modifier d'autres point de la formule ?

Répondre
Frédéric LE GUEN 25/01/2021 at 15:51

Oui, oui, oui, .... un peu de patience.
Je sais que je dois le faire mais j'ai mille tâches à réaliser avant.
Ce n'est pas simple d'être le dernier MVP en langue Française. J'ai bcp de sujet à couvrir.
Mais ça sera fait.

Répondre
Ephram 26/01/2021 at 15:49

Ok merci de l'article et des conseils super pratiques.

Répondre
Frédéric LE GUEN 25/01/2021 at 22:35

Mais pour soixante-dix, c'est bien septante, comme en Belgique

Répondre
Charles 21/01/2021 at 10:08

Chapeau ! Pourtant, à la lecture de la formule définitive, j'ai tout de suite vu... que c'est un véritable casse-tête !
Par contre, j'adore l'idée de le faire avec des formules SANS MACRO !
Bravo !

Répondre
Frédéric LE GUEN 21/01/2021 at 15:14

C'est sur, il y a eu du travail. C'est pour cela que j'ai partager la fonction car c'est impossible de créer ce genre de formule de toute pièce. Et les macros, si on peut les éviter, c'est bien mieux

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.