Forum Discussion

Bhavya250203's avatar
Bhavya250203
Copper Contributor
Aug 09, 2022

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 ...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 10, 2022

SergeiBaklan 

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位)

 

 

SergeiBaklan's avatar
SergeiBaklan
MVP
Aug 11, 2022

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?

  • Stevenson Yu's avatar
    Stevenson Yu
    Brass Contributor
    Nov 07, 2024

    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"
))

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Nov 07, 2024

      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" )

       

      • Stevenson Yu's avatar
        Stevenson Yu
        Brass Contributor
        Nov 08, 2024

        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 馃槄

  • Bhavya250203's avatar
    Bhavya250203
    Copper Contributor
    Aug 11, 2022
    May be we can call them Custom LAMBDA Function ??

Resources