Inspiration for NumToDollars Formula
Inspired by the reply of John Alsdorf, I modified my Excel NumToWords Formula to consider Dollars as a currency built into it. I’ll explain the modification I did to my NumToWords formula here.
Parts of NumToDollars Formula
My NumToDollars formula is a defined name that comprises twenty-two (22) other defined names, of which the first fourteen (14) were explained in my previous post and the next eight (8) will be explained in the succeeding sections. Such names use these twelve (12) built-in functions: ABS, IF, INDEX, INT, LOOKUP, MID, MOD, REPT, RIGHT, ROUND, TEXT, and TRUNC.
1. Defined Name for CurName
CurNum refers to:
=REPT("Dollar",INT(IntNum)>0)&REPT("s",INT(IntNum)>1)INT rounds down "001234567890123" to 1234567890123 and the number_times argument of the second REPT evaluates to TRUE, which is equivalent to 1. So, the formula above evaluates to:
=REPT("Dollar",1234567890123>0)&"s"Given that the number_times argument evaluates to 1, REPT evaluates to "Dollar". So, the formula above evaluates to:
="Dollars"To convert number to another Currency, such as Pesos, please replace "Dollar" with "Peso".2. Defined Name for CurNameTxt
CurNameTxt refers to:
=REPT(IntTxt&CurName,INT(IntNum)>0)Given that IntTxt evaluates to "One Trillion Two Hundred Thirty-four Billion Five Hundred Sixty-seven Million Eight Hundred Ninety Thousand One Hundred Twenty-three ", CurName evaluates to "Dollars", and the number_times argument of REPT evaluates to 1, the formula above evaluates to:
="One Trillion Two Hundred Thirty-four Billion Five Hundred Sixty-seven Million Eight Hundred Ninety Thousand One Hundred Twenty-three Dollars"3. Defined Name for DecNum
DecNum refers to:
=ROUND(ABS(CellLeft-TRUNC(CellLeft))*100,0)TRUNC truncates -1234567890123.45 to an integer by removing its fractional part. So, the formula above evaluates to:
=ROUND(ABS(-1234567890123.45--1234567890123)*100,0)ABS converts the fractional part -0.45 to its absolute value. So, the formula above evaluates to:
=ROUND(0.45*100,0)ROUND rounds 45 to the nearest integer. So, the formula above evaluates to:
=454. Defined Name for DecOne
DecOne refers to:
=INT(RIGHT(DecNum))RIGHT returns the last character in 45. So, the formula above evaluates to:
=INT("5")INT rounds down "5" to the nearest integer. So, the formula above evaluates to:
=55. Defined Name for DecNumTxt
DecNumTxt refers to:
=IF(DecNum=0,"",IF(DecNum<20,LOOKUP(DecNum,NumList,TxtList1),IF(MOD(DecNum,10)=0,LOOKUP(DecNum,NumList,TxtList1),LOOKUP(DecNum,NumList,TxtList2))))The last LOOKUP returns a text. Given that DecNum evaluates to 45, the formula above evaluates to:
=IF(45=0,"",IF(45<20,LOOKUP(45,NumList,TxtList1),IF(MOD(45,10)=0,LOOKUP(45,NumList,TxtList1),"Forty-")))The remaining LOOKUPs return texts. So, the formula above evaluates to:
=IF(45=0,"",IF(45<20,"Forty ",IF(MOD(45,10)=0,"Forty ","Forty-")))MOD returns a number. So, the above formula evaluates to:
=IF(45=0,"",IF(45<20,"Forty ",IF(5=0,"Forty ","Forty-")))The last IF returns a text. So, the formula above evaluates to:
=IF(45=0,"",IF(45<20,"Forty ","Forty-"))
The second IF returns a text. So, the formula above evaluates to:
=IF(45=0,"","Forty-")The remaining IF returns a text. So, the formula above evaluates to:
="Forty-"6. Defined Name for DecOneTxt
DecOneTxt refers to:
=IF(DecNum<20,"",IF(MOD(DecNum,10)=0,"",LOOKUP(DecOne,NumList,TxtList2)))LOOKUP returns a text. Given that DecNum evaluates to 45 and DecOne evaluates to 5, the formula above evaluates to:
=IF(45<20,"",IF(MOD(45,10)=0,"","five "))MOD returns a number. So, the formula above evaluates to:
=IF(45<20,"",IF(5=0,"","five "))The second IF returns a text. So, the formula above evaluates to:
=IF(45<20,"","five ")The remaining IF returns a text. So, the formula above evaluates to:
="five "7. Defined Name for DecName
DecName refers to:
="Cent"&REPT("s",DecNum>1)Given that DecNum evaluates to 45 and the number_times argument evaluates to TRUE, which is equivalent to 1, REPT concatenates "s" to "Cent". So, the formula above evaluates to:
="Cents"8. Defined Name for DecNameTxt
DecNameTxt refers to:
=REPT(REPT(" and ",INT(IntNum)>0)&DecNumTxt&DecOneTxt&DecName,DecNum>0)INT rounds down "001234567890123" to 1234567890123 and the number_times argument of the second REPT evaluates to TRUE, which is equivalent to 1. So, the formula above evaluates to:
=REPT(" and "&"Forty-five Cents,DecNum>0)Given that DecNum evaluates to 45, the number_times argument of the remaining REPT evaluates to TRUE, which is equivalent to 1. So, the formula above evaluates to:
="and Forty-five Cents"Defined Name for NumToDollars
NumToDollars refers to:
=REPT("Negative ",CellLeft<0)&CurNameTxt&DecNameTxtGiven that CellLeft evaluates to -1234567890123.45, the number_times argument of REPT evaluates to TRUE, which is equivalent to 1. So, the formula above evaluates to:
="Negative One Trillion Two Hundred Thirty-four Billion Five Hundred Sixty-seven Million Eight Hundred Ninety Thousand One Hundred Twenty-three Dollars and Forty-five Cents"If you replaced "Dollar" with "Peso" in the definition of CurName, and "Cent"with "Centavo" in the definition of DecName, please replace NumToDollars with NumToPesos.Syntax and Usage of NumToDollars
Similar to NumToWords, NumtoDollars converts any number (within 15 significant digits) to the left of the active cell to Dollars and can be used as an argument of Text Functions in all versions of Microsoft Excel.
Copy of NumToDollars Formula
Please download the Excel file here and copy my NumToDollars formula therein. After testing its syntax and usage, do apprise me of your thoughts thereon through a comment below!
Comments