Exemple:
valeur | sortie souhaitée |
---|---|
300 | trois cents |
300,50 | Trois cent 50 cents |
Je ne trouve pas de moyen de le faire dans Google Sheets et je ne suis pas assez averti pour créer quoi que ce soit.
6 Réponses :
IMPORTRANGE
pour importer la valeur dans F6 et un autre IMPORTRANGE
pour réimporter le valeur convertie de B11 retour Ce site Web https: / /www.excelforum.com/tips-and-tutorials/1015010-convert-a-numeric-value-to-words-without-vba.html
Affiche une formule Excel qui peut faire cela. Je viens de tester dans Google Sheets et cela fonctionne également.
La formule est
=IF(OR(LEN(FLOOR(E3,1))>=13,FLOOR(E3,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(E3>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(E3,1)>1," dollars"," dollar")))
funciona perfecto. En mi caso cambié las comas por punto y coma y funcionó sin problemas.
Petits ajustements ici et là de la réponse de dominic pour répondre exactement à vos besoins: strong > =REGEXREPLACE(IF(OR(LEN(FLOOR(A1,1))>=13,FLOOR(A1,1)<=0),"Out of range",
PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),
CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
IF(A1>=10^9," billion ",""),
CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),
CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),
CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),
CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(A1),
REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),
REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," "))&
IF(IFERROR(QUERY({A1},"where Col1 Contains '.'"),)<>"",
" and "®EXEXTRACT(TO_TEXT(A1), "\.(.+)")&" cents", ))," ", " ")
Votre édition produit également les décimales, non?
@Khan oui en effet
Vous pouvez essayer le module complémentaire Numbertext =NUMBERTEXT("EUR 25") => twenty-five euro
=MONEYTEXT(25,"EUR") => twenty-five euro
Réponse parfaite, facile, fiable et meilleure !!!
Comme il s'agit spécifiquement de Google Sheet, vous pouvez utiliser ce
300 three hundred dollars and no cents 300.5 three hundred dollars and fifty cents
où A1 est le nombre que vous souhaitez convertir
Crédit: formules de nombres de sorts ou nombre de mots . Il existe également de nombreuses solutions alternatives dans ce lien, par exemple voici une méthode qui utilise la formule matricielle
=ArrayFormula(if(A2:A="","",if(len(int(A2:A))<13,"",if(right(left(int(A2:A),len(int(A2:A))-12),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-12),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-12),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-12),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-12),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-12),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" trillion")&" "&if(len(int(A2:A))<10,"",if(right(left(int(A2:A),len(int(A2:A))-9),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-9),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-9),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-9),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-9),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-9),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" billion")&" "&if(len(int(A2:A))<7,"",if(right(left(int(A2:A),len(int(A2:A))-6),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-6),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-6),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-6),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-6),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-6),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" million")&" "&if(len(int(A2:A))<4,"",if(right(left(int(A2:A),len(int(A2:A))-3),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-3),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-3),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-3),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-3),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-3),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" thousand")&" "&if(len(int(A2:A))<1,"",if(right(left(int(A2:A),len(int(A2:A))-0),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-0),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-0),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-0),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-0),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-0),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")))&if(int(A2:A)=0,"0 dollar",if(int(A2:A)<2," dollar"," dollars"))&iferror(" and "&if(round(mod(A2:A,1)*100,2)>19,choose(left(mod(A2:A,1)*100)-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(round(mod(A2:A,1)*100,2))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(round(mod(A2:A,1)*100,2)+1,"no","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" cents")))
La sortie est
=REGEXEXTRACT(JOIN(", ",QUERY(IMPORTDATA("https://www.calculatorsoup.com/calculators/conversions/numberstowords.php?number=" & A1 & "&format=words&letter_case=lowercase&action=solve"),"where Col1 contains '<div id=""answer""><br>'")),">([^<]+)")
Il suffit de changer le A2: A
de la formule matricielle à la position correcte si vos nombres sont dans un position différente
Une autre façon est de cloner les 2 feuilles déjà créées ci-dessous en sélectionnant Fichier> Faire une copie puis en utilisant
Crédit: Convertisseur de montant ou de nombres en mots à l'aide de Google Sheets . L'auteur a également introduit des versions plus récentes qui ne nécessitent qu'une seule feuille. Si vous souhaitez utiliser ces clonez-le ici
Vous pouvez utiliser cette méthode pour convertir votre devise en mots:
function convertToWords(input) { var a, b, c, d, e, output, outputA, outputB, outputC, outputD, outputE; var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine']; if (input === 0) { // Zero output = "Rupees zero"; } else if (input == 1) { // One output = "Rupee one only"; } else { // More than one // Tens a = input % 100; outputA = oneToHundred_(a); // Hundreds b = Math.floor((input % 1000) / 100); if (b > 0 && b < 10) { outputB = ones[b]; } // Thousands c = (Math.floor(input / 1000)) % 100; outputC = oneToHundred_(c); // Lakh d = (Math.floor(input / 100000)) % 100; outputD = oneToHundred_(d); // Crore e = (Math.floor(input / 10000000)) % 100; outputE = oneToHundred_(e); // Make string output = "Rupees"; if (e > 0) { output = output + " " + outputE + " crore"; } if (d > 0) { output = output + " " + outputD + " lakh"; } if (c > 0) { output = output + " " + outputC + " thousand"; } if (b > 0) { output = output + " " + outputB + " hundred"; } if (input > 100 && a > 0) { output = output + " and"; } if (a > 0) { output = output + " " + outputA; } output = output + " only"; } return output; } function oneToHundred_(num) { var outNum; var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine']; var teens = ['ten', 'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen']; var tens = ['', '', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety']; if (num > 0 && num < 10) { // 1 to 9 outNum = ones[num]; // ones } else if (num > 9 && num < 20) { // 10 to 19 outNum = teens[(num % 10)]; // teens } else if (num > 19 && num < 100) { // 20 to 100 outNum = tens[Math.floor(num / 10)]; // tens if (num % 10 > 0) { outNum = outNum + " " + ones[num % 10]; // tens + ones } } return outNum; }