View original (French)

Convert numbers to words with a single formula

FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   -  
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   -
Bonjour les Ami(e)s,

Would there be someone who could correct this unique formula invented by one of the users of this forum that converts numbers into words in Excel 365? The problem is that this formula does not display the cents.

The formula is as follows:

NOMPROPRE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SI(NBCAR(ENT(A19))<=2;RECHERCHEV(STXT(ENT(A19);1;3)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=3;RECHERCHEV(GAUCHE(ENT(A19);1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);2;3)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=4;RECHERCHEV(GAUCHE(ENT(A19);1)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);2;1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);3;3)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=5;RECHERCHEV(GAUCHE(ENT(A19);2)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);3;1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);4;2)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=6;RECHERCHEV(GAUCHE(ENT(A19);1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);2;2)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);4;1)*1;chiffre_100;2;0)" cents ""&RECHERCHEV(STXT(ENT(A19);5;2)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=7;RECHERCHEV(GAUCHE(ENT(A19);1)*1;chiffre_100;2;0)" millions "&RECHERCHEV(STXT(ENT(A19);2;1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);3;2)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);5;1)*1;chiffre_100;2;0)" cents ""&RECHERCHEV(STXT(ENT(A19);6;2)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=8;RECHERCHEV(GAUCHE(ENT(A19);2)*1;chiffre_100;2;0)" millions "&RECHERCHEV(STXT(ENT(A19);3;1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);4;2)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);6;1)*1;chiffre_100;2;0)" cents ""&RECHERCHEV(STXT(ENT(A19);7;2)*1;chiffre_100;2;0);SI(NBCAR(ENT(A19))=9;RECHERCHEV(GAUCHE(ENT(A19);1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);2;2)*1;chiffre_100;2;0)" millions "&RECHERCHEV(STXT(ENT(A19);4;1)*1;chiffre_100;2;0)" cents "&RECHERCHEV(STXT(ENT(A19);5;2)*1;chiffre_100;2;0)" thousand "&RECHERCHEV(STXT(ENT(A19);7;1)*1;chiffre_100;2;0))" cents "&RECHERCHEV(STXT(ENT(A19);8;2)*1;chiffre_100;2;0))))))));"zero cents ";);"one cent ";"cent ");"Zero thousand";);"Zero";"");"one thousand ";" thousand ");"zero";);"one millions ";" one million ");" thousand "" ")&" EURO "&SUBSTITUE(RECHERCHEV(SIERREUR(STXT(A19+0.001;TROUVE(",";A19+0.001)+1;2)*1;0);chiffre_100;2;0)" cents";"one cents";"one cent"))

18 réponses

Réponse 1 / 18
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509
 
Hi FOUAD

I just saw this about installing the NBLettre add-on on Office 365. I knew about it but wasn't sure if it was possible; well, I hope it is ;)

https://microboum.blogspot.com/2019/09/excel-office-365-convertir-les-nombres.html

Best regards
1
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

The module has been around for ages, not just Excel 365; I remembered it only displayed the letters, not the currency, my bad

It is criticized for a number of inaccuracies in the expression of the result, but these can be corrected for a clean output from the code, as discussed here:

https://forum.excel-pratique.com/excel/macro-complementaire-nb-lettre-xla-124216
0
FOUAD > brucine Posted messages 24411 Registration date   Status Membre Last intervention  
 
Hello everyone, and thank you so much for your efforts. I already know this module, but what I'm interested in is a single and unique formula. It's a matter of challenge and convenience for some beginner users in Excel.
0
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104 > FOUAD
 
Hello,

For a reason I don't understand, your formula as posted in <8> works including in Excel 2019 if I go to the cell in B2 and press enter after the last character of the formula 
one cent"))


If I change the value in A2, the formula does not "refresh" unless I again press enter in B2 in the same way.

Of course, there remains, but this is another question, to make the appropriate corrections: 

Seven Hundred Twenty-Four Million Five Hundred Seventy-Two Thousand Four Hundred Fifty-Seven Euros Thirty-Three Cents



Two Thousand Four Hundred Fifty-Seven Euros Zero Cents


There is no reason to use capital letters (and therefore PROPER), hyphens, or spaces between values.
The accent is missing on Zero and Cent in singular (however correct for 2457.01 or 2457.02).
0
Réponse 2 / 18
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509
 
Hello,

Or a function created by VBA code taken from this discussion

https://www.cjoint.com/c/LBkgQsup8B8

Best regards
0
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509
 
In the last concatenated part of the formula, replace the dot with a comma for 0.001 &SUBSTITUTE(VLOOKUP(IFERROR(MID(A19+0.001, SEARCH(",",A19+0.001)+1, 2)*1, 0), chiffre_100, 2, 0)&" Centimes","un Centimes","un centime")

However, I cannot verify this formula because the area processed by chiffre_100 is missing.
0
FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   1
 
It still doesn't work, have you tested it? If so, please send me an Excel file with the test.
=PROPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(ENT(A19))<=2,VLOOKUP(MID(ENT(A19),1,3)*1,number_100,2,0),IF(LEN(ENT(A19))=3,VLOOKUP(LEFT(ENT(A19),1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),2,3)*1,number_100,2,0),IF(LEN(ENT(A19))=4,VLOOKUP(LEFT(ENT(A19),1)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),2,1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),3,3)*1,number_100,2,0),IF(LEN(ENT(A19))=5,VLOOKUP(LEFT(ENT(A19),2)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),3,1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),4,2)*1,number_100,2,0),IF(LEN(ENT(A19))=6,VLOOKUP(LEFT(ENT(A19),1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),2,2)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),4,1)*1,number_100,2,0)&" hundred "&" "&VLOOKUP(MID(ENT(A19),5,2)*1,number_100,2,0),IF(LEN(ENT(A19))=7,VLOOKUP(LEFT(ENT(A19),1)*1,number_100,2,0)&" millions "&VLOOKUP(MID(ENT(A19),2,1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),3,2)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),5,1)*1,number_100,2,0)&" hundred "&" "&VLOOKUP(MID(ENT(A19),6,2)*1,number_100,2,0),IF(LEN(ENT(A19))=8,VLOOKUP(LEFT(ENT(A19),2)*1,number_100,2,0)&" millions "&VLOOKUP(MID(ENT(A19),3,1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),4,2)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),6,1)*1,number_100,2,0)&" hundred "&" "&VLOOKUP(MID(ENT(A19),7,2)*1,number_100,2,0),IF(LEN(ENT(A19))=9,VLOOKUP(LEFT(ENT(A19),1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),2,2)*1,number_100,2,0)&" millions "&VLOOKUP(MID(ENT(A19),4,1)*1,number_100,2,0)&" hundred "&VLOOKUP(MID(ENT(A19),5,2)*1,number_100,2,0)&" thousand "&VLOOKUP(MID(ENT(A19),7,1)*1,number_100,2,0))&" hundred "&VLOOKUP(MID(ENT(A19),8,2)*1,number_100,2,0))))))));"zero hundred ";);"one hundred ";"hundred ");"Zero thousand";);"Zero";"");"one thousand ";" thousand ");"zero";);"one million ";" one million ");" thousand ";" ")&" EURO "&SUBSTITUTE(VLOOKUP(ERROR.TYPE(MID(A19+0.001,FIND(",",A19+0.001)+1,2)*1,0),number_100,2,0)&" Cents","one Cents","one cent")
0
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509 > FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention  
 
A closing parenthesis is missing at the end of "one cent" ))

On my test (Excel 2013) it returns the error #NAME? because the area processed by the name chiffre_100 is missing

If you want us to check, we would need a sample file without confidential data and with this named area
Go to https://www.cjoint.com/

•Click on "BROWSE" to select the file
Or
•"DRAG AND DROP" into the window;

Click on the "CREATE LINK" button

To copy/paste the link: (2 methods)

•Right-click on the created link and click on "COPY LINK ADDRESS"; on the post right-click "PASTE".
•"COPY" the link; on the post open the bracket " Alt+[ "; "PASTE" the link; close with the bracket " Alt+] "

Other sites:
https://mon-partage.fr/
https://www.transfernow.net/


Best regards
0
Réponse 3 / 18
FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   1
 
Hello, thank you for your kind response but I am looking for a single and unique formula without VBA.
0
FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   1
 
https://www.cjoint.com/c/LBkiAqvACUN
0
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509 > FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention  
 
RE:

A detail that I just saw, the calculation of formulas in manual mode.

I don't know how it is on Excel 365 - on 2013 > tab FORMULAS > Calculation Options > click on "Automatic"

There are quite a few things to review in this formula (see the few examples in the return file

https://www.cjoint.com/c/LBkmMojFlZ8

I also found in my archives a file without VBA to adapt if needed (in the sheet "monnaies" make hidden rows visible to understand)

https://www.cjoint.com/c/LBkmMtgPCh8

Best regards
0
FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   1 > PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention  
 
Thank you so much anyway for the efforts you made to help me, but I already know this file without VBA. My wish is to have one single formula. Once again, a thousand thanks.
0
Réponse 4 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

I can't test it since I don't have Excel 365 or 2021, which have two specific functions, so I only have a few remarks to make.

Why go through PROPER, in other words, why would the sums in words be capitalized?

In the same vein, why "one million" or "one cents" or "cents"?

Note that, since we're talking about specific Excel 365 functions, another solution is proposed here with the LET function:

https://www.excel-exercice.com/convertir-un-nombre-en-lettres/

That said, why in a more general way want to go through such a convoluted process when a single simple formula does the job, provided you have a second sheet with the data and certainly some simple calculations that won't appear in the printout?
0
FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention   1
 
Could you please send me an Excel file with a conversion case using the LET function?
0
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104 > FOUAD_9000 Posted messages 63 Registration date   Status Membre Last intervention  
 
The formula is in the link I mentioned, at the very bottom, starting from the numbers in A1.
0
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104 > brucine Posted messages 24411 Registration date   Status Membre Last intervention  
 
Regarding the syntax of the LET function:

https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999

About your previous formula, the last 2 lines are obviously not interpreted, and it's quite tedious to find the bug in such a complex formula.

You need to start by testing this last formula separately.

As far as I can tell, it seems to me that there is a nesting issue with the IF functions; even if the length (NBCAR) is just 4, we will go through the entire file, the condition being negative each time, but apparently these last 2 lines only execute if the length (NBCAR) is 9.

So start by checking what happens in this case; you may need to replace all the formulas while keeping the same parenthesis structure with simple answers to the IF conditions (“toto”, etc.) to verify that the order of the conditions is respected and that you are correctly linked to the last expression in all cases.
0
Réponse 5 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
I reviewed and corrected the typos in the name range chiffre_100;2;0, deleted NOMPROPRE which doesn't belong there, corrected a few typos in the formula itself.

There are still at least some on the conditional ones conjugating euro in plural (if greater than 2) and centime in singular (if there are zero)

For the first one, replace at the bottom of the file "& & " euros " with &SI(A2>=2;" euros ";"euro ")

I have a bug below 2 euros that I haven't localized yet: uneuro fifty three

In the second one, it's the SIERREUR...TROUVE formula that's messed up, I don't have time to look for it right now.

And still about having to validate the formula every time to update it, I don't know why, maybe due to its length.

https://www.cjoint.com/c/LBplXGVG3bO
0
Réponse 6 / 18
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Hello everyone,

I'm joining the ongoing discussion, if you set the calculation to automatic, the formula updates.
File/Options/Formula section/check Workbook Calculation Automatic, then save the file

however, if we enter 0.1 or 0.01 the formula returns euro ten cents or euro one cent

if I have a moment this afternoon I'll look into either removing euro before the decimals if there aren't any, or displaying 0 euro etc ...
See you later
Mike-31

I am responsible for what I say, not for what you understand...
0
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

Yes, I'm as dumb as a hay bale with a pitchfork; with automatic calculation, it's indeed better.

It's not just "euro" that's at stake
0.01 makes euro one cent, 1.00 makes euro zero cents, 1000.03 has a superfluous space between thousand and euros, same for 10000,
100000 makes us stuck at hundred euros zero cents
If I win the lottery at 10883366.08, there's a superfluous space between hundred and sixty

Haven't found anything else through polling for now, there's still some work to correct all this.
0
FOUAD > brucine Posted messages 24411 Registration date   Status Membre Last intervention  
 
Hello, thank you for your efforts, I am eagerly awaiting this formula.
0
Réponse 7 / 18
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

Don't blame yourself, everyone gets stuck on an obvious solution sometimes
look at the formula like this that takes the plural of Euros as well as cents

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(ENT(A2))<=2,VLOOKUP(MID(ENT(A2),1,3)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=3,VLOOKUP(LEFT(ENT(A2),1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),2,3)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=4,VLOOKUP(LEFT(ENT(A2),1)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),2,1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),3,3)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=5,VLOOKUP(LEFT(ENT(A2),2)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),3,1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),4,2)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=6,VLOOKUP(LEFT(ENT(A2),1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),2,2)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),4,1)*1, chiffre_100,2,0)&" cent "&" "&VLOOKUP(MID(ENT(A2),5,2)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=7,VLOOKUP(LEFT(ENT(A2),1)*1, chiffre_100,2,0)&" millions "&VLOOKUP(MID(ENT(A2),2,1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),3,2)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),5,1)*1, chiffre_100,2,0)&" cent "&" "&VLOOKUP(MID(ENT(A2),6,2)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=8,VLOOKUP(LEFT(ENT(A2),2)*1, chiffre_100,2,0)&" millions "&VLOOKUP(MID(ENT(A2),3,1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),4,2)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),6,1)*1, chiffre_100,2,0)&" cent "&" "&VLOOKUP(MID(ENT(A2),7,2)*1, chiffre_100,2,0),IF(LEN(ENT(A2))=9,VLOOKUP(LEFT(ENT(A2),1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),2,2)*1, chiffre_100,2,0)&" millions "&VLOOKUP(MID(ENT(A2),4,1)*1, chiffre_100,2,0)&" cent "&VLOOKUP(MID(ENT(A2),5,2)*1, chiffre_100,2,0)&" mille "&VLOOKUP(MID(ENT(A2),7,1)*1, chiffre_100,2,0))&" cent "&VLOOKUP(MID(ENT(A2),8,2)*1, chiffre_100,2,0))))))));&"zéro cent ";);&"un cent ";&"cent ");&"zéro mille";);&"zéro";&"");&"un mille ";&" mille ");&"zéro";);&"un million ";&" un million ");&" mille ";&" ")&TEXT(ENT(A2),"[>1]" Euros "[>]" Euro "")&SUBSTITUTE(VLOOKUP(IFERROR(MID(A2+0.001,SEARCH(",",A2+0.001)+1,2)*1,0),chiffre_100,2,0)&"&""&""&"&TEXT(ENT((A2-ENT(A2))*100),"[>1]" Centimes "[>]" Centime "")

indeed I notice that the code caps after 99999.99
for my part I will take a look in the evening
--
A+
Mike-31

I am responsible for what I say, not for what you understand...
0
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
No, Mike, you are stuck above 1000 where you're missing "thousand."
0
Réponse 8 / 18
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

To handle all scenarios with the initial formula, personally I'm reaching saturation with the conditionals of the field chiffre_100 as well as the formula and good luck intervening in this maze.
Why not switch to basic formulas in a column that can be hidden?
If it's of interest, I'll start by naming the target cell (the cell for entering the value), in my example I simply named it Target
then the formulas in the range A2:A94, like the Target cell, can be CUT (moved) and the formula will update automatically.

Then the cells D5, D7, or D9 display several Formats of display based on the concatenated cell A94

https://www.cjoint.com/c/LBqrpz8IsxF
Cheers
Mike-31

I am responsible for what I say, not for what you understand...
0
Réponse 9 / 18
FOUAD
 
Hello and thank you all,

I am aware of all the options you suggested, but my wish is for a single and unique formula. It's a challenge to take on, good luck.
0
Réponse 10 / 18
PapyLuc51 Posted messages 4566 Registration date   Status Membre Last intervention   1 509
 
Greetings to all,

I give up.

Wishing you all the best.
0
Réponse 11 / 18
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

So if this is a challenge, name the cell where you will enter your value, for my formula the example cell B2 named Target will avoid entering the cell address multiple times and generating an error.
Of course, the name Target can be changed after validation and the formula will automatically take the new name.

Next, paste this formula into a cell, but good luck following it 

=IF(INT(Target)=0;"ZERO ";IF(MOD(INT(Target/10^9);1000)>99;CHOOSE(MOD(INT(Target/10^9);1000)/100;;"TWO ";"THREE ";"FOUR ";"FIVE ";"SIX ";"SEVEN ";"EIGHT ";"NINE ")&"HUNDRED"&IF(MOD(INT(Target/10^9);1000)>100;IF(MOD(MOD(INT(Target/10^9);1000);100)=0;"S";" "););)&CHOOSE(MOD(MOD(INT(Target/10^9);1000)/10;10)+1;;"TWENTY""THIRTY""FORTY""FIFTY""SIXTY""SIXTY""EIGHTY""&IF(MOD(MOD(INT(Target/10^9);1000);10)=0;"S";);"EIGHTY")&IF(MOD(MOD(INT(Target/10^9);1000);100)<20;;IF(AND(OR(MOD(MOD(INT(Target/10^9);1000);10)<>0;MOD(MOD(INT(Target/10^9);1000);100)=70;MOD(MOD(INT(Target/10^9);1000);100)=90));IF(AND(MOD(MOD(INT(Target/10^9);1000);10)=1;MOD(MOD(INT(Target/10^9);1000);100)<80);" AND ";"-");))&CHOOSE(MOD(MOD(INT(Target/10^9);1000);100)-CHOOSE(MOD(MOD(INT(Target/10^9);1000)/10;10)+1;;;2;3;4;5;6;6;8;8)*10+1;"ONE""TWO""THREE""FOUR""FIVE""SIX""SEVEN""EIGHT""NINE""TEN""ELEVEN""TWELVE""THIRTEEN""FOURTEEN""FIFTEEN""SIXTEEN""SEVENTEEN""EIGHTEEN""NINETEEN")&IF(MOD(INT(Target/10^9);1000)>0;" BILLION"&IF(MOD(INT(Target/10^9);1000)>1;"S";);)&IF(Target>10^9;" ";)&IF(MOD(INT(Target/10^6);1000)>99;CHOOSE(MOD(INT(Target/10^6);1000)/100;;"TWO ";"THREE ";"FOUR ";"FIVE ";"SIX ";"SEVEN ";"EIGHT ";"NINE ")&"HUNDRED"&IF(MOD(INT(Target/10^6);1000)>100;IF(MOD(MOD(INT(Target/10^6);1000);100)=0;"S";" "););)&CHOOSE(MOD(MOD(INT(Target/10^6);1000)/10;10)+1;;"TWENTY""THIRTY""FORTY""FIFTY""SIXTY""SIXTY""EIGHTY"&IF(MOD(MOD(INT(Target/10^6);1000);10)=0;"S";);"EIGHTY")&IF(MOD(MOD(INT(Target/10^6);1000);100)<20;;IF(AND(OR(MOD(MOD(INT(Target/10^6);1000);10)<>0;MOD(MOD(INT(Target/10^6);1000);100)=70;MOD(MOD(INT(Target/10^6);1000);100)=90));IF(AND(MOD(MOD(INT(Target/10^6);1000);10)=1;MOD(MOD(INT(Target/10^6);1000);100)<80);" AND ";"-");))&CHOOSE(MOD(MOD(INT(Target/10^6);1000);100)-CHOOSE(MOD(MOD(INT(Target/10^6);1000)/10;10)+1;;;2;3;4;5;6;6;8;8)*10+1;"ONE""TWO""THREE""FOUR""FIVE""SIX""SEVEN""EIGHT""NINE""TEN""ELEVEN""TWELVE""THIRTEEN""FOURTEEN""FIFTEEN""SIXTEEN""SEVENTEEN""EIGHTEEN""NINETEEN")&IF(MOD(INT(Target/10^6);1000)>0;" MILLION"&IF(MOD(INT(Target/10^6);1000)>1;"S";);)&IF(Target>10^6;" ";)&IF(MOD(INT(Target/10^3);1000)>1;IF(MOD(INT(Target/10^3);1000)>99;CHOOSE(MOD(INT(Target/10^3);1000)/100;;"TWO ";"THREE ";"FOUR ";"FIVE ";"SIX ";"SEVEN ";"EIGHT ";"NINE ")&"HUNDRED"&IF(MOD(INT(Target/10^3);1000)>100;IF(MOD(MOD(INT(Target/10^3);1000);100)=0;"S";" "););)&CHOOSE(MOD(MOD(INT(Target/10^3);1000)/10;10)+1;;"TWENTY""THIRTY""FORTY""FIFTY""SIXTY""SIXTY""EIGHTY"&IF(MOD(MOD(INT(Target/10^3);1000);10)=0;"S";);"EIGHTY")&IF(MOD(MOD(INT(Target/10^3);1000);100)<20;;IF(AND(OR(MOD(MOD(INT(Target/10^3);1000);10)<>0;MOD(MOD(INT(Target/10^3);1000);100)=70;MOD(MOD(INT(Target/10^3);1000);100)=90));IF(AND(MOD(MOD(INT(Target/10^3);1000);10)=1;MOD(MOD(INT(Target/10^3);1000);100)<80);" AND ";"-");))&CHOOSE(MOD(MOD(INT(Target/10^3);1000);100)-CHOOSE(MOD(MOD(INT(Target/10^3);1000)/10;10)+1;;;2;3;4;5;6;6;8;8)*10+1;"ONE""TWO""THREE""FOUR""FIVE""SIX""SEVEN""EIGHT""NINE""TEN""ELEVEN""TWELVE""THIRTEEN""FOURTEEN""FIFTEEN""SIXTEEN""SEVENTEEN""EIGHTEEN""NINETEEN")&&" THOUSAND";IF(MOD(INT(Target/10^3);1000)=1;"THOUSAND";))&IF(Target>10^3;" ";))&IF(INT(Target-INT(Target/1000)*1000)>0;IF(INT(Target-INT(Target/1000)*1000)>99;CHOOSE(INT(Target-INT(Target/1000)*1000)/100;;"TWO ";"THREE ";"FOUR ";"FIVE ";"SIX ";"SEVEN ";"EIGHT ";"NINE ")&"HUNDRED"&IF(INT(Target-INT(Target/1000)*1000)>100;IF(MOD(INT(Target-INT(Target/1000)*1000);100)=0;"S";" "););)&CHOOSE(MOD(INT(Target-INT(Target/1000)*1000)/10;10)+1;;"TWENTY""THIRTY""FORTY""FIFTY""SIXTY""SIXTY""EIGHTY"&IF(MOD(INT(Target-INT(Target/1000)*1000);10)=0;"S";);"EIGHTY")&IF(MOD(INT(Target-INT(Target/1000)*1000);100)<20;;IF(AND(OR(MOD(INT(Target-INT(Target/1000)*1000);10)<>0;MOD(INT(Target-INT(Target/1000)*1000);100)=70;MOD(INT(Target-INT(Target/1000)*1000);100)=90));IF(AND(MOD(INT(Target-INT(Target/1000)*1000);10)=1;MOD(INT(Target-INT(Target/1000)*1000);100)<80);" AND ";"-");))&CHOOSE(MOD(INT(Target-INT(Target/1000)*1000);100)-CHOOSE(MOD(INT(Target-INT(Target/1000)*1000)/10;10)+1;;;2;3;4;5;6;6;8;8)*10+1;"ONE""TWO""THREE""FOUR""FIVE""SIX""SEVEN""EIGHT""NINE""TEN""ELEVEN""TWELVE""THIRTEEN""FOURTEEN""FIFTEEN""SIXTEEN""SEVENTEEN""EIGHTEEN""NINETEEN");)&IF(INT(INT(Target-INT(Target/1000)*1000))>0;" ";"&&"EURO"&IF(INT(Target)>1;"S";)&IF(ROUND((Target-INT(Target))*100;2)>0;" "&IF(ROUND((Target-INT(Target))*100;2)>99;CHOOSE(ROUND((Target-INT(Target))*100;2)/100;;"TWO ";"THREE ";"FOUR ";"FIVE ";"SIX ";"SEVEN ";"EIGHT ";"NINE ")&"HUNDRED"&IF(ROUND((Target-INT(Target))*100;2)>100;IF(MOD(ROUND((Target-INT(Target))*100;2);100)=0;"S";" "););)&CHOOSE(MOD(ROUND((Target-INT(Target))*100;2)/10;10)+1;;"TWENTY""THIRTY""FORTY""FIFTY""SIXTY""SIXTY""EIGHTY"&IF(MOD(ROUND((Target-INT(Target))*100;2);10)=0;"S";);"EIGHTY")&IF(MOD(ROUND((Target-INT(Target))*100;2);100)<20;;IF(AND(OR(MOD(ROUND((Target-INT(Target))*100;2);10)<>0;MOD(ROUND((Target-INT(Target))*100;2);100)=70;MOD(ROUND((Target-INT(Target))*100;2);100)=90));IF(AND(MOD(ROUND((Target-INT(Target))*100;2);10)=1;MOD(ROUND((Target-INT(Target))*100;2);100)<80);" AND ";"-");))&CHOOSE(MOD(ROUND((Target-INT(Target))*100;2);100)-CHOOSE(MOD(ROUND((Target-INT(Target))*100;2)/10;10)+1;;;2;3;4;5;6;6;8;8)*10+1;"ONE""TWO""THREE""FOUR""FIVE""SIX""SEVEN""EIGHT""NINE""TEN""ELEVEN""TWELVE""THIRTEEN""FOURTEEN""FIFTEEN""SIXTEEN""SEVENTEEN""EIGHTEEN""NINETEEN")&&" CENT"&IF(ROUND((Target-INT(Target))*100;2)>1;"S";);)


https://www.cjoint.com/c/LBrjIBwCIxF

A+
Mike-31

I am responsible for what I say, not for what you understand...
0
Réponse 12 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

In OFF mode, Fouad found a recipe on the Internet filled with spelling mistakes and functional errors.

As repeatedly stated, the solution is very simple via a second sheet (or a data area in the first), and it's likely simple in VBA as well; I'm completely incompetent in that, but he doesn't want to hear about either one.

Just like in the 80s, when the 4 or 5 batch wizards demonstrated unbelievable things without using any utilities just for the pleasure of showing that it was possible, there is probably a solution using a single formula.

It's all the more unpalatable because it goes on for miles, making it practically impossible to spot errors; everything has to be redone from scratch by testing the formula for each order of magnitude separately, a Herculean task.

If I have a lot of time to waste, I will take up the challenge that serves no other purpose than the motto of the polytechnic school (for the homeland, the sciences, and glory); for now, it works up to €999.99 in all scenarios, including the twisted ones (less than €1, less than €2, isolated or absent cents, absence of tens digit for example in €200.83): there are only 4 more steps to test and nest to reach the millions, which is nonsensical in terms of time spent complicating what isn’t complicated.

Finally, let’s note that Fouad only requests a ready-to-use formula telegraphically and repetitively without ever making any modifications himself, even just correcting typos (for example, "treise" (sic) and other inconsistencies in the table), while claiming to target beginners who will be even less capable of mastering a formula of such complexity than he is.
0
Réponse 13 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

The most complicated part of this matter is less about the formulas than the grammar rules that I'm summarizing:
- "thousand" is invariable, "80" and "100" only agree when not followed, the plural starts at "2".
- In traditional grammar, only compound numbers below "100" have a hyphen and when they do not end with one: in this case, the hyphen only applies to "81" and "91", otherwise, we use "and".
- The "recommendations" of 1990 (thus without normative value) propose to link all numerals with a hyphen, raising a debate about the status of "million" and "billion" which are not numerals.

Whether the subject is monetary or not, only the first letter in the title (Fifty Shades of Grey) or at the beginning of a sentence (Thirty points were scored by the pivot) takes a capital letter; in monetary matters, the question usually won't arise in either case.

On the substance of the "exercise", the simplest solution and one accessible for beginners, since it is said to be included in the specifications, is to use a separate sheet.

Even though it’s absurd, Mike took up the challenge to fit everything into a single formula; this inevitably makes it complex and lengthy since the value of the numbers must be integrated (and also because it operates on 14 significant digits less than one trillion euros).

Since the initial formula (which did not work) already included a table of names, I propose an alternative with a formula that includes 3 (to account for cases of "1" and "0" without a conditional) but that, out of laziness, I only extended it to 9 significant digits less than ten million; it is easy to add as many as desired using the template.
Personally, I chose the rule of traditional grammar, not so much because it is easier to implement but because I find it less painful to look at: here too, to each their own.

The heaviness of the formulas makes typographical errors almost inevitable.
Mike chose to disregard "PROPER NAME" and "CAPITAL LETTER," but we've just seen that they have no place there...by writing everything in uppercase.
There is also a typographical error with the plural of hundred: eight hundred fifty-four instead of eight hundred fifty-four.

Those interested in the subject (not sure if there are any besides Fouad) might want to point out mine.

https://www.cjoint.com/c/LBupkoGKfMO
0
Réponse 14 / 18
FOUAD
 
Hello everyone,

Thank you for all your efforts, I believe we are on the right track, soon we will find one and only formula. Keep it up.
0
Réponse 15 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Hello,

What are you talking about?

The solution implemented by Mike in <26> only affects a single formula; I just proposed one in <28> that, like your initial formula in <1>, goes through the name manager (with 3 defined names instead of one) and still without an extra sheet or VBA, but which, unlike formula <1>, works.
0
Réponse 16 / 18
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

It would be desirable for FOUAD to respond to each post if he wants us to make progress, like for example to my post 26 to allow for improvement or fixing a bug.
As for soon we will find one and only one formula. Keep it up does not bring any advancement to the discussion.

Brucine, I just took a look at your proposal post 28, it seems that 0.01, 0.1, and 1.01 are not displaying correctly.
I didn't go further.
If you notice a bug on my proposal post 26, please report it if it is possible to remedy it.

--
A+
Mike-31

I am responsible for what I say, not for what you understand...
0
Réponse 17 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Thank you,

0.1 was working, but in my frenzy to create additional name tables, I accidentally modified the original table chiffre_100 with the value of 1 as "" instead of "one" and this table is only used for cents...
0
Réponse 18 / 18
brucine Posted messages 24411 Registration date   Status Membre Last intervention   4 104
 
Re, Mike:

I converted your formula to lowercase; it was hurting my eyes.

You have a bug in the plural of 80 when it is followed: for example in 101080.20, there is an unnecessary "s" in twenty that I should only find in 101080.00.

I thought I found something comparable in the expression of hundred only in certain circumstances (we write four hundred euros but two hundred and twenty-four euros), I must have been dreaming, I can't find it again.
0
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Hi,

Thank you for these observations, I'll keep them in mind to look into as soon as possible. If you see anything else, don't hesitate, it's how we move forward even if such a formula remains hard to digest.
0