GOOGLE SHEET WORKSHEET FUNCTION - NUMBER IN "RINGGIT" & "SEN" MALAYSIA CONVERTER (LIMITTED VERSION - ONLY ALLOW 6 DIGIT TO THE LEFT OF THE DECIMAL)
(1) This worksheet function contain 4783 characters and it does not work on EXCEL work sheet because EXCELsheet only allow 255 characters into a single cell. By the way excel sheet has its own converter build in some 6-8 years ago and is written in visual basic. Google sheet can take 50,000 characters.
(2) Just cut and paste the worksheet function below. You can use it in your invoice, Cheque writer or legal document.
(3) This version is limited to 6 digit to the left of the decimal point. If the digit is more than 6, the function will tell you that you have exceeded the 6 digit allowed.
(4) Round your number to 2 decimal places.
Where to place your function on the sheet?
(a) For the purpose of this illustration place or paste the function below in cell B1
(b) Input your number in cell A1.... easy..easy
Note : Make sure your number is not formmated with comas. Round your number to 2 decimal places, that is, you have 2 digit after a dot/decimal.
How to use it on your invoice?
Change the Cell location, eg in the function below the location of the formula is in cell A1. If your total invoice value is in cell K15, then change all the A1 to K15 by using any text processing app "Find and Replace". Once you changed the cell, the function will show the converted result.
Full version of up to almost Quadrillion ie {(10^15)-0.01} in English (US, English Canadian, Australian, and modern British), Bahasa Malaysia in zip file at USD30.00. Can customize in other languages or dialect too. Function in JavaScript. Please inbox me.
Selamat mencuba
Mohdar Ismail
Illustration
---------------------------------------------------------------------------------------------------------------
="Ringgit Malaysia :"&" "&(TRIM(IF(OR((value(left(A1,(len(A1)-3))))>999999,(value(left(A1,(len(A1)-3))))<1),"This version is limited to 6 digit to the left of a decimal or dot and rounded to two decimal places",(IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),6))>99999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),6))<1000000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),1))+1)&" "&"Hundred"&" "&IF(VALUE(INDEX({"100000","200000","300000","400000","500000","600000","700000","800000","900000"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),1))))/1000=VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),3)),"Thousand",""),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>19999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<100000),INDEX({"Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),1)-1)),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>19999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<100000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),2),1)+1)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>9999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<20000),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),2))-9)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<10000),CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),4),1))+1)," ","Thousand"),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),3))>99, VALUE(RIGHT((value(left(A1,(len(A1)-3)))),3))<1000), CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),3),1))+1)," ","Hundred"),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>19,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<100),INDEX({"","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1, (VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),2),1)))),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>9,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<20),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,RIGHT((value(left(A1,(len(A1)-3)))),1)+1),"")&" "&IF(OR(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>20,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<10),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT((value(left(A1,(len(A1)-3)))),1)+1),""))))&" "&"And"&" "&"Sen :"&" "& (TRIM(IF(OR(A1>999999,A1<1),"This version is limited to 6 digit (integer only)",(IF(AND(VALUE(RIGHT(A1,6))>99999,VALUE(RIGHT(A1,6))<1000000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,6),1))+1)&" "&"Hundred"&" "&IF(VALUE(INDEX({"100000","200000","300000","400000","500000","600000","700000","800000","900000"},1,VALUE(LEFT(RIGHT(A1,6),1))))/1000=VALUE(LEFT(RIGHT(A1,6),3)),"Thousand",""),"")&" "&IF(AND(VALUE(RIGHT(A1,5))>19999,VALUE(RIGHT(A1,5))<100000),INDEX({"Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1,VALUE(LEFT(RIGHT(A1,5),1)-1)),"")&" "&IF(AND(VALUE(RIGHT(A1,5))>19999,VALUE(RIGHT(A1,5))<100000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(LEFT(RIGHT(A1,5),2),1)+1)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT(A1,5))>9999,VALUE(RIGHT(A1,5))<20000),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,VALUE(LEFT(RIGHT(A1,5),2))-9)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT(A1,5))>999,VALUE(RIGHT(A1,5))<10000),CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,4),1))+1)," ","Thousand"),"")&" "&IF(AND(VALUE(RIGHT(A1,3))>99, VALUE(RIGHT(A1,3))<1000), CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,3),1))+1)," ","Hundred"),"")&" "&IF(AND(VALUE(RIGHT(A1,2))>19,VALUE(RIGHT(A1,2))<100),INDEX({"","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1, (VALUE(LEFT(RIGHT(A1,2),1)))),"")&" "&IF(AND(VALUE(RIGHT(A1,2))>9,VALUE(RIGHT(A1,2))<20),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,RIGHT(A1,1)+1),"")&" "&IF(OR(VALUE(RIGHT(A1,2))>20,VALUE(RIGHT(A1,2))<10),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(A1,1)+1),"")))))&" "&"Only")
Alternatively you can copy and paste the following code by:
Cut and paste the code into google <> Script editor and run it by pressing keyboard keys - Ctrl+Alt+Shift+2
/** @OnlyCurrentDoc */
function N2W() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B1').activate();
spreadsheet.getCurrentCell().setFormula('="Ringgit Malaysia :"&" "&(TRIM(IF(OR((value(left(A1,(len(A1)-3))))>999999,(value(left(A1,(len(A1)-3))))<1),"This version is limited to 6 digit to the left of a decimal or dot and rounded to two decimal places",(IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),6))>99999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),6))<1000000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),1))+1)&" "&"Hundred"&" "&IF(VALUE(INDEX({"100000","200000","300000","400000","500000","600000","700000","800000","900000"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),1))))/1000=VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),6),3)),"Thousand",""),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>19999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<100000),INDEX({"Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),1)-1)),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>19999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<100000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),2),1)+1)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>9999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<20000),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),5),2))-9)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))>999,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),5))<10000),CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),4),1))+1)," ","Thousand"),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),3))>99, VALUE(RIGHT((value(left(A1,(len(A1)-3)))),3))<1000), CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),3),1))+1)," ","Hundred"),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>19,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<100),INDEX({"","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1, (VALUE(LEFT(RIGHT((value(left(A1,(len(A1)-3)))),2),1)))),"")&" "&IF(AND(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>9,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<20),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,RIGHT((value(left(A1,(len(A1)-3)))),1)+1),"")&" "&IF(OR(VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))>20,VALUE(RIGHT((value(left(A1,(len(A1)-3)))),2))<10),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT((value(left(A1,(len(A1)-3)))),1)+1),""))))&" "&"And"&" "&"Sen :"&" "& (TRIM(IF(OR(A1>999999,A1<1),"This version is limited to 6 digit (integer only)",(IF(AND(VALUE(RIGHT(A1,6))>99999,VALUE(RIGHT(A1,6))<1000000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,6),1))+1)&" "&"Hundred"&" "&IF(VALUE(INDEX({"100000","200000","300000","400000","500000","600000","700000","800000","900000"},1,VALUE(LEFT(RIGHT(A1,6),1))))/1000=VALUE(LEFT(RIGHT(A1,6),3)),"Thousand",""),"")&" "&IF(AND(VALUE(RIGHT(A1,5))>19999,VALUE(RIGHT(A1,5))<100000),INDEX({"Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1,VALUE(LEFT(RIGHT(A1,5),1)-1)),"")&" "&IF(AND(VALUE(RIGHT(A1,5))>19999,VALUE(RIGHT(A1,5))<100000),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(LEFT(RIGHT(A1,5),2),1)+1)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT(A1,5))>9999,VALUE(RIGHT(A1,5))<20000),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,VALUE(LEFT(RIGHT(A1,5),2))-9)&" "&"Thousand","")&" "&IF(AND(VALUE(RIGHT(A1,5))>999,VALUE(RIGHT(A1,5))<10000),CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,4),1))+1)," ","Thousand"),"")&" "&IF(AND(VALUE(RIGHT(A1,3))>99, VALUE(RIGHT(A1,3))<1000), CONCATENATE(INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,VALUE(LEFT(RIGHT(A1,3),1))+1)," ","Hundred"),"")&" "&IF(AND(VALUE(RIGHT(A1,2))>19,VALUE(RIGHT(A1,2))<100),INDEX({"","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"},1, (VALUE(LEFT(RIGHT(A1,2),1)))),"")&" "&IF(AND(VALUE(RIGHT(A1,2))>9,VALUE(RIGHT(A1,2))<20),INDEX({"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},1,RIGHT(A1,1)+1),"")&" "&IF(OR(VALUE(RIGHT(A1,2))>20,VALUE(RIGHT(A1,2))<10),INDEX({"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"},1,RIGHT(A1,1)+1),"")))))&" "&"Only")');
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().setValue('123');
spreadsheet.getActiveSheet().setColumnWidth(2, 533);
spreadsheet.getRange('B1').activate();
spreadsheet.getActiveRangeList().setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
.setVerticalAlignment('top');
spreadsheet.getRange('A1').activate();
spreadsheet.getActiveRangeList().setVerticalAlignment('top');
};
No comments:
Post a Comment