Sometimes it may be necessary to show an amount in words, rather than numerically. For the Indian numbering system of crores and lakhs the formula I usually use needed some tweaking - I'm hoping all is OK, as this is my first exposure to this numbering system.
A VBA User-Defined-Function could do this easily (well, easier than the effort of creating this formula), but sometimes the usage of VBA is not desirable, or maybe not possible at all.
This formula is very VERY long, as it replicates logic for 3 groups of 2 numbers and then a group of 3 numbers, being, left-to-right: crore, lakh, thousand, and 1-to-999. Each digit is examined, by doing a MID function call on a TEXT version of the number ... the MID function fetches a particular sub-string from a lump of text, or even just a single character, as in the cases within this formula. The actual formula is shown below, but it may be easier to view an example in an Excel worksheet, which is downloadable here.
Formula: =CHOOSE(MID(TEXT(B2,"000000000.00"),1,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000000000.00"),1,1)<>1,CHOOSE(MID(TEXT(B2,"000000000.00"),2,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B2,"000000000.00"),2,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&IF((--LEFT(TEXT(B2,"000000000.00"))+MID(TEXT(B2,"000000000.00"),2,1))=0,,IF(AND((--MID(TEXT(B2,"000000000.00"),4,1)+MID(TEXT(B2,"000000000.00"),5,1)+MID(TEXT(B2,"000000000.00"),6,1)+MID(TEXT(B2,"000000000.00"),7,1))=0,(--MID(TEXT(B2,"000000000.00"),8,1)+RIGHT(TEXT(B2,"000000000.00")))>0)," Crore and "," Crore "))
&CHOOSE(MID(TEXT(B2,"000000000.00"),3,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(B2,"000000000.00"),3,1)<>1,CHOOSE(MID(TEXT(B2,"000000000.00"),4,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B2,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(B2,"000000000.00"),3,1)+MID(TEXT(B2,"000000000.00"),4,1))=0,,IF(OR((--MID(TEXT(B2,"000000000.00"),5,1)+--MID(TEXT(B2,"000000000.00"),6,1)+--MID(TEXT(B2,"000000000.00"),7,1)+MID(TEXT(B2,"000000000.00"),8,1)+MID(TEXT(B2,"000000000.00"),9,1))=0,--MID(TEXT(B2,"000000000.00"),7,1)<>0)," Lakh "," Lakh "))
&CHOOSE(MID(TEXT(B2,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(B2,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B2,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B2,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(B2,"000000000.00"),5,1)+MID(TEXT(B2,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B2,"000000000.00"),8,1)+MID(TEXT(B2,"000000000.00"),9,1))=0,--MID(TEXT(B2,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))
&CHOOSE(MID(TEXT(B2,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(B2,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B2,"000000000.00"),8,1)=0,--MID(TEXT(B2,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&
CHOOSE(MID(TEXT(B2,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B2,"000000000.00"),9,1)+1,IF(INT(B2)=0,"Zero",""),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B2,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&" Rupees & "&CHOOSE(MID(TEXT(B2,"000000000.00"),11,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000000000.00"),11,1)<>1,CHOOSE(MID(TEXT(B2,"000000000.00"),12,1)+1,IF(MID(TEXT(B2,"000000000.00"),11,1)*1=0,"Zero",""),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B2,"000000000.00"),12,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&" Paise"
King Of Excel
Copyright © 2021 Glenn Bamford - All Rights Reserved.
Powered by GoDaddy Website Builder