Forum Discussion
Convert Number to Words LAMBDA (Very Large Numbers)
That's interesting to compare with PeterBartholomew1 formula suggested couple of years ago https://techcommunity.microsoft.com/t5/excel-blog/announcing-let/ba-p/1233572
=LET(
Denom, {" Trillion ";" Billion ";" 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;4;5},
LET(
N, SUBSTITUTE( TEXT( A1, 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, 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, "" ) )
)
)Lot of changes in Excel from that time.
Will play with both some later.
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?
- May be we can call them Custom LAMBDA Function ??
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" )
