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 Number and second is Optional (By default Indian No. System, 2 for International No. System, 1 for Indian)

 

It can convert up to 100 Trillion (International Number System) and Lakhs of Crores (Indian Number System).

 

Avoided conversion after decimal for now but that can be implemented as well. If Number has decimal, function will Round it first and then give results.

 

YT Video Link - https://www.youtube.com/watch?v=u1gzAcwmlpo

 

Gist (GitHub) Link - https://gist.github.com/Bhavya2502/8413a0e6af783ad18e72419eca47ad09

 

What are your thoughts on this ?

 

PeterBartholomew1  SergeiBaklan  

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 09, 2022

    Bhavya250203 

    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.

    • Bhavya250203's avatar
      Bhavya250203
      Copper Contributor
      Aug 10, 2022

      SergeiBaklan My goal was actually two fold for both Indian as well as International Number System. Both are very different in terms of place value of numbers. I first made for Indian and then adapted as per International System (With few addons, Some steps and Lookup Tables were common).

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 10, 2022

        Bhavya250203 

        Please accept my apologies for conducting an exchange with SergeiBaklan on your thread.

        I must admit, I wondered how you dealt with the more irregular patterns of Lakh and Crore (I find the number formatting to be enough of a challenge).  Presumably you could get some benefit from having  multiple groups of 2 digits rather than my 5 groups of 3 digits?

    • 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?

Resources