Search for Similar Formulas
My Google search for an Excel formula to convert number to words returned these results:
User Defined Functions, such as Number2Words, NumberAsText, NumberToWords, NumsToWords, NumToWords, NUMWORD, spellCurrency, SPELLDOLLAR, SpellNumber, and SpellNumberEDP.
Esoteric Built-in Formulas, such as those created by Jamil Mohammad, ndthanh, Peter Menhennet, and The FrankensTeam.
Inspired by the foregoing works, I created my Excel NumToWords Formula, the process of which I’ll explain here.
Parts of NumToWords Formula
My NumToWords formula is a defined name that comprises sixteen (16) other defined names, each of which is explained in the succeeding sections. Such names use these eleven (11) built-in functions: ABS, IF, INDEX, INT, LOOKUP, MID, MOD, REPT, ROUND, TEXT, and TRUNC.
1. Defined Name for CellLeft
If the value of A2 is -1234567890123.45 and the active cell is B2, CellLeft refers to:
=!A2Thus, CellLeft always refers to the cell to the left of the active cell.2. Defined Name for IntNum
IntNum refers to:
=TEXT(INT(ABS(CellLeft)),"000000000000000")ABS converts -1234567890123.45 to its absolute value. So, the formula above evaluates to:
=TEXT(INT(1234567890123.45),"000000000000000")INT rounds 1234567890123.45 down to the nearest integer. So, the formula above evaluates to:
=TEXT(1234567890123,"000000000000000")
TEXT formats 1234567890123 as a 15-digit text. So, the formula above evaluates to:
=”001234567890123”3. Defined Name for HunNum
HunNum refers to:
=INT(MID(IntNum,{1;4;7;10;13},1))MID extracts the hundreds places of ”001234567890123”. So, the formula above evaluates to:
=INT({“0”;”2”;”5”;”8”;”1”})INT converts {“0”;”2”;”5”;”8”;”1”} to numbers. So, the formula above evaluates to:
={0;2;5;8;1}4. Defined Name for TenNum
TenNum refers to:
=INT(MID(IntNum,{2;5;8;11;14},2))MID extracts the tens and ones places of ”001234567890123”. So, the formula above evaluates to:
=INT({“01”;”34”;”67”;”90”;”23”})INT converts {“01”;”34”;”67”;”90”;”23”} to numbers. So, the formula above evaluates to:
={1;34;67;90;23}5. Defined Name for OneNum
OneNum refers to:
=INT(MID(IntNum,{3;6;9;12;15},1))MID extracts the ones places of ”001234567890123”. So, the formula above evaluates to:
=INT({“1”;”4”;”7”;”0”;”3”})INT converts {“1”;”4”;”7”;”0”;”3”} to numbers. So, the formula above evaluates to:
={1;4;7;0;3}6. Defined Name for SfxNum
SfxNum refers to:
=INT(MID(IntNum,{1;4;7;10},3))MID extracts the hundreds, tens, and ones places of the first 12 digits of ”001234567890123”. So, the formula above evaluates to:
=INT({“001”;”234”;”567”;”890”})INT converts {“001”;”234”;”567”;”890”} to numbers. So, the formula above evaluates to:
={1;234;567;890}7. Defined Name for NumList
NumList creates a vertical array of numbers and refers to:
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;30;40;50;60;70;80;90}8. Defined Name for TxtList1
TxtList1 creates a vertical array of texts and refers to:
={"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen ";"Twenty ";"Thirty ";"Forty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety ”}
9. Defined Name for TxtList2
TxtList2 creates a vertical array of texts and refers to:
={"one ";"two ";"three ";"four ";"five ";"six ";"seven ";"eight ";"nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen ";"Twenty-";"Thirty-";"Forty-";"Fifty-";"Sixty-";"Seventy-";"Eighty-";"Ninety-”}10. Defined Name for SfxList
SfxList refers to:
=IF(SfxNum,{"Trillion ";"Billion ";"Million ";"Thousand "},{"";"";"";""})IF creates a vertical array of texts. Given that SfxNum evaluates to {1;234;567;890}, the formula above evaluates to:
={"Trillion ";"Billion ";"Million ";"Thousand "}11. Defined Name for HunTxt
HunTxt refers to:
=IF(HunNum=0,"",LOOKUP(HunNum,NumList,TxtList1)&"Hundred ")LOOKUP creates a vertical array of texts. Given that HunNum evaluates to {0;2;5;8;1}, the formula above evaluates to:
=IF({0;2;5;8;1}=0,"",{#N/A;“Two ”;”Five ”;”Eight ”;”One ”}&"Hundred ")IF creates a vertical array of texts. So, the formula above evaluates to:
={“”;“Two Hundred ”;”Five Hundred ”;”Eight Hundred ”;”One Hundred ”}12. Defined Name for TenTxt
TenTxt refers to:
=IF(TenNum=0,"",IF(TenNum<20,LOOKUP(TenNum,NumList,TxtList1),IF(MOD(TenNum,10)=0,LOOKUP(TenNum,NumList,TxtList1),LOOKUP(TenNum,NumList,TxtList2))))The last LOOKUP creates a vertical array of texts. Given that TenNum evaluates to {1;34;67;90;23}, the formula above evaluates to:
=IF({1;34;67;90;23}=0,"",IF({1;34;67;90;23}<20,LOOKUP({1;34;67;90;23},NumList,TxtList1),IF(MOD({1;34;67;90;23},10)=0,LOOKUP({1;34;67;90;23},NumList,TxtList1),{“one ”;”Thirty-”;”Sixty-”;”Ninety-”;”Twenty-”})))The remaining LOOKUPs create vertical arrays of texts. So, the formula above evaluates to:
=IF({1;34;67;90;23}=0,"",IF({1;34;67;90;23}<20,{“One ”;”Thirty ”;”Sixty ”;”Ninety ”;”Twenty ”},IF(MOD({1;34;67;90;23},10)=0,{“One ”;”Thirty ”;”Sixty ”;”Ninety ”;”Twenty ”},{“one ”;”Thirty-”;”Sixty-”;”Ninety-”;”Twenty-”})))MOD creates a vertical array of numbers. So, the above formula evaluates to:
=IF({1;34;67;90;23}=0,"",IF({1;34;67;90;23}<20,{“One ”;”Thirty ”;”Sixty ”;”Ninety ”;”Twenty ”},IF({1;4;7;0;3}=0,{“One ”;”Thirty ”;”Sixty ”;”Ninety ”;”Twenty ”},{“one ”;”Thirty-”;”Sixty-”;”Ninety-”;”Twenty-”})))The last IF creates a vertical array of texts. So, the formula above evaluates to:
=IF({1;34;67;90;23}=0,"",IF({1;34;67;90;23}<20,{“One ”;”Thirty ”;”Sixty ”;”Ninety ”;”Twenty ”},{“one ”;”Thirty-”;”Sixty-”;”Ninety ”;”Twenty-”}))The second IF creates a vertical array of texts. So, the formula above evaluates to:
=IF({1;34;67;90;23}=0,"",{“One “;”Thirty-”;”Sixty-”;”Ninety ”;”Twenty-”})The remaining IF creates a vertical array of texts. So, the formula above evaluates to:
={“One “;”Thirty-”;”Sixty-”;”Ninety ”;”Twenty-”}
13. Defined Name for OneTxt
OneTxt refers to:
=IF(TenNum<20,"",IF(MOD(TenNum,10)=0,"",LOOKUP(OneNum,NumList,TxtList2)))LOOKUP creates a vertical array of texts. Given that TenNum evaluates to {1;34;67;90;23} and OneNum evaluates to {1;4;7;0;3}, the formula above evaluates to:
=IF({1;34;67;90;23}<20,"",IF(MOD({1;34;67;90;23},10)=0,"",{“one ”;”four ”;”seven ”;#N/A;”three ”}))MOD creates a vertical array of numbers. So, the formula above evaluates to:
=IF({1;34;67;90;23}<20,"",IF({1;4;7;0;3}=0,"",{“one ”;”four ”;”seven ”;#N/A;”three ”}))The second IF creates a vertical array of texts. So, the formula above evaluates to:
=IF({1;34;67;90;23}<20,"",{“one ”;”four ”;”seven ”;””;”three ”})The remaining IF creates a vertical array of texts. So, the formula above evaluates to:
={“”;”four ”;”seven ”;””;”three ”}14. Defined Name for IntTxt
IntTxt refers to:
=INDEX(HunTxt,1)&INDEX(TenTxt,1)&INDEX(OneTxt,1)&INDEX(SfxList,1)&INDEX(HunTxt,2)&INDEX(TenTxt,2)&INDEX(OneTxt,2)&INDEX(SfxList,2)&INDEX(HunTxt,3)&INDEX(TenTxt,3)&INDEX(OneTxt,3)&INDEX(SfxList,3)&INDEX(HunTxt,4)&INDEX(TenTxt,4)&INDEX(OneTxt,4)&INDEX(SfxList,4)&INDEX(HunTxt,5)&INDEX(TenTxt,5)&INDEX(OneTxt,5)The concatenated INDEXes convert 1234567890123 to words. So, 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 "15. 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:
=4516. Defined Name for DecTxt
DecTxt refers to:
=REPT("& ",ABS(CellLeft)>=1)&DecNum&"/100"ABS converts -1234567890123.45 to its absolute value. So, the formula above evaluates to:
=REPT("& ",1234567890123.45>=1)&"45/100"Given that the number_times argument evaluates to TRUE, which is equivalent to 1, REPT returns “& “. So, the formula above evaluates to:
=”& 45/100”Defined Name for NumToWords
NumToWords refers to:
=REPT("Negative ",CellLeft<0)&IntTxt&DecTxtGiven that the number_times argument evaluates to TRUE, which is equivalent to 1, REPT returns “Negative ”. 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 & 45/100"Syntax and Usage of NumToWords
NumToWords converts any number (within 15 significant digits) to the left of the active cell to words. It can be used as an argument of Text Functions in all versions of Microsoft Excel, examples of which are itemized below:
In the formula below, CODE returns 78:
=CODE(NumToWords)In the formula below, FIND returns 36:
=FIND("h",NumToWords)In the formula below, LEFT returns “Negative One Trillion”:
=LEFT(NumToWords,21)In the formula below, LEN returns 150:
=LEN(NumToWords)In the formula below, LOWER returns "negative one trillion two hundred thirty-four billion five hundred sixty-seven million eight hundred ninety thousand one hundred twenty-three & 45/100":
=LOWER(NumToWords)In the formula below, MID returns “Two Hundred Thirty-four Billion”:
=MID(NumToWords,23,31)In the formula below, PROPER returns "Negative One Trillion Two Hundred Thirty-Four Billion Five Hundred Sixty-Seven Million Eight Hundred Ninety Thousand One Hundred Twenty-Three & 45/100":
=PROPER(NumToWords)In the formula below, REPLACE returns "Negative One Trillion Two Cientos Thirty-four Billion Five Hundred Sixty-seven Million Eight Hundred Ninety Thousand One Hundred Twenty-three & 45/100":
=REPLACE(NumToWords,27,7,"Cientos")In the formula below, REPT returns “”:
=REPT(NumToWords,1>2)In the formula below, RIGHT returns "One Hundred Twenty-three & 45/100":
=RIGHT(NumToWords,33)In the formula below, SEARCH returns 27:
=SEARCH("h",NumToWords)In the formula below, SUBSTITUTE returns "Negative One Trillion Two Cientos Thirty-four Billion Five Cientos Sixty-seven Million Eight Cientos Ninety Thousand One Cientos Twenty-three & 45/100":
=SUBSTITUTE(NumToWords,"Hundred","Cientos")In the formula below, UNICODE returns 78:
=UNICODE(NumToWords)In the formula below, UPPER returns "NEGATIVE ONE TRILLION TWO HUNDRED THIRTY-FOUR BILLION FIVE HUNDRED SIXTY-SEVEN MILLION EIGHT HUNDRED NINETY THOUSAND ONE HUNDRED TWENTY-THREE & 45/100":
=UPPER(NumToWords)Copy of NumToWords Formula
I originally published this post in LinkedIn and subsequently in Medium. Please download the Excel file here and copy my NumToWords formula therein. After testing its syntax and usage, do apprise me of your thoughts thereon through a comment below!
Comentarios