Forum Discussion
Convert Number to Words LAMBDA (Very Large Numbers)
I created this LAMBDA Function "Number_To_Words" in order to convert Numbers to Words (eg. 2813 can be written as Two Thousand Eight Hundred Thirteen in words) First Parameter of the function is ...
Thank you for the reference. Due to a clumsy transfer of data to a new computer, I had lost the original English version and was going to reconstruct it from a German language version I had written or the French version Frederic LE GUEN had developed from the original. The original workbook predates the CONCAT function and relied upon a multitude of defined names to conduct array calculations pre-dynamic-array.
Something that occurred when I tried to set up a new workbook from the published formula was that the formula refused to commit. I tracked that back to the removal of '.' (period) as a valid character within a LET local name.
TextDollarAmountλ
= LAMBDA(target,
LET(
Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},
Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
Tens, {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"},
grp, {0;1;2;3},
N, SUBSTITUTE( TEXT( target, REPT(0,9)&".00" ),".","0"),
H, VALUE( MID( N, 3*grp+1, 1) ),
T, VALUE( MID( N, 3*grp+2, 1) ),
U, VALUE( MID( N, 3*grp+3, 1) ),
Htxt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),
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 ) ) ),
CONCAT(IF(H+T+U, Htxt & Ttxt & Utxt & Denom, "") )
)
);
The good thing about turning the formula into a Lambda function is that it can be applied to many values within a workbook.
= MAP(target, TextDollarAmountλ)
PeterBartholomew1 , actually I took your initial formula in the comments to the blog here with LET() announcement . Removed dot in names, added "Billion" to Denom and expanded grp (from 0 to 5).
Yes, these days it's more reasonable to use it as custom function. By the way, UDF is reserved for VBA functions, will we use the same abbreviation for lambdas or one day another term appears?
Thank you for leaving your lambda for the English version of BAHTTEXT.
I modified it a bit to fix the edge cases (it fails when the tens and ones digit is 0, such as 100 (appends a FALSE), 1000, 10000, and so on (leaves out the DOLLAR). I also extended this to 9,999,999,999,999.
My workarounds are in no way beautiful, and I'm sure there are more elegant solutions that fix the underlying issue. But, my hack-job works for the edge cases I throw at it. 😅
=LAMBDA(Amount, LET( Denom, {" Trillion ";" Billion ";" Million ";" Thousand ";" Dollar and ";" Cent"}, Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}, Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}, Tens, {"","Ten","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}, grp, {0;1;2;3;4;5}, N, SUBSTITUTE(TEXT(Amount, REPT(0,15) & ".00"), ".", "0"), H, VALUE(MID(N, 3 * grp + 1, 1)), T, VALUE(MID(N, 3 * grp + 2, 1)), U, VALUE(MID(N, 3 * grp + 3, 1)), Htxt, IF(H > 0, INDEX(Nums, H + 1) & " Hundred ", ""), Ttxt, IF(T > 1, INDEX(Tens, T + 1) & IF(U > 0, "-", ""), IF(T = 1, INDEX(Teens, U + 1), "")), Utxt, IF(T = 1, "", IF(U > 0, INDEX(Nums, U + 1), "")), Words, UPPER(CONCAT(IF((H + T + U) > 0, Htxt & Ttxt & Utxt & INDEX(Denom, grp + 1), ""))), Conj, IF(RIGHT(Words, 5) = " AND ", LEFT(Words, LEN(Words) - 5), Words), Comp, IF(AND(INT(Amount) = Amount, NOT(ISNUMBER(SEARCH("DOLLAR", Conj)))), Conj & " DOLLAR", Conj), Pl1, IF(INT(Amount) <> 1, SUBSTITUTE(Comp, "DOLLAR", "DOLLARS"), Comp), Pl2, IF(TEXT(Amount - INT(Amount), "0.00") > "0.01", SUBSTITUTE(Pl1, "CENT", "CENTS"), Pl1), TRIM(Pl2) & " ONLY" ))Someone suggested to use BAHTTEXT() and TRANSLATE() (so far only on Beta), it looks like
=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( TRANSLATE(BAHTTEXT(F11)), "baht", "Dollar" ), "satang", "cent" ), ".", " only" )I did think of using it, but rejected it because (1) it requires an online connection, (2) it doesn't respect singular/plural forms for currency, (3) it gives inconsistent cases that throws off SUBSTITUTE - for example, 10 and 100,000 renders as "Baht", but 100, 1,000 and 10,000 renders as "baht", (4) the translation isn't perfect - for example, 11,112,222 is rendered as "eleven million, one hundred and eleven, twelve thousand, two hundred and twenty-two dollar only", and (5) it doesn't handle billions well 😅
- May be we can call them Custom LAMBDA Function ??
