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.
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).
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?
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.
[Added. Only the first number had no decimal digits, so MAP works OK with your function]
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?
- 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.
I agree with PeterBartholomew1 , 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().
