# Convert Number to Words LAMBDA (Very Large Numbers)

Copper Contributor

# 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.

What are your thoughts on this ?

11 Replies

# Re: Convert Very Large Number to Words LAMBDA

That's interesting to compare with @Peter Bartholomew 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.

# Re: Convert Very Large Number to Words LAMBDA

@Sergei Baklan 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).

# Re: Convert Very Large Number to Words LAMBDA

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λ)``

# Re: Convert Very Large Number to Words LAMBDA

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?

# Re: Convert Very Large Number to Words LAMBDA

I have now downloaded your Lambda function.  Congratulations, there is nothing short in terms of ambition in that function!  I did try

``= MAP(target, format, Indian.Number_To_Words)``

where 'format' is a range containing {1;0;1} but it only returned the first number.

The "Indian" prefix came about because I specified a Name space; I have never done that before.

The only thing I wondered about was whether it is necessary to be able to switch between international and local practice for reporting numbers?  Wouldn't two functions have done the job as well and permitted some further optimisation of the individual code?

# Re: Convert Very Large Number to Words LAMBDA

Thanks a lot for the praise. Actually I originally made the function as per Indian Number System but then thought of adding more flexibility of choosing between the two systems as many steps were common in them.

# Re: Convert Very Large Number to Words LAMBDA

@Peter Bartholomew , 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?

# Re: Convert Very Large Number to Words LAMBDA

I agree with @Peter Bartholomew , it's better to separate functions for each language. If only your are not going to generalize it and include few dozens of languages.

That's normal practice. For example, we don't have FILL() function with parameters Up and Down, we have FillUp() and FillDown().

# Re: Convert Very Large Number to Words LAMBDA

May be we can call them Custom LAMBDA Function ??

# Re: Convert Very Large Number to Words LAMBDA

Yes you are right Sir. Separating gives clarity as well :thumbs_up:

# Re: Convert Very Large Number to Words LAMBDA

Hi All,

I need a formula for numbers to words without a VBA code. My currency is Taka instead of Dollars and after the decimal, it's called Paisa instead of Cent. Also require the word Only, in the last.
Here is the sample for a better understanding
1 = One Taka Only
10 = Ten Taka Only
10.50 = Ten Taka Fifty Paisa Only
100 = One Hundred Taka Only
1000 = One Thousand Taka Only
10000 = Ten Thousand Taka Only
100000 = One Lac Taka Only (We called Lac for one hundred thousand)
1000000 = Ten Lac Taka Only
10000000 = One Crore Taka Only