Have you ever thought of an Excel formula that will convert Numbers to Words? i.e. A formula that can convert the number 456,571 into Four Hundred and Fifty Six Thousand Five Hundred and Seventy One.
In this blog post, I am sharing two Excel formulas that can convert Numbers in Words.
Table of Contents
Pete M’s Formula
=CHOOSE(LEFT(TEXT(A1,"000000000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(A1,"000000000"))=0,,IF(AND(--MID(TEXT(A1,"000000000"),2,1)=0,--MID(TEXT(A1,"000000000"),3,1)=0)," Hundred"," Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000"),2,1)<>1,CHOOSE(MID(TEXT(A1,"000000000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(A1,"000000000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(A1,"000000000"))+MID(TEXT(A1,"000000000"),2,1)+MID(TEXT(A1,"000000000"),3,1))=0,,IF(AND((--MID(TEXT(A1,"000000000"),4,1)+MID(TEXT(A1,"000000000"),5,1)+MID(TEXT(A1,"000000000"),6,1)+MID(TEXT(A1,"000000000"),7,1))=0,(--MID(TEXT(A1,"000000000"),8,1)+RIGHT(TEXT(A1,"000000000")))>0)," Million and "," Million "))&CHOOSE(MID(TEXT(A1,"000000000"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(A1,"000000000"),4,1)=0,,IF(AND(--MID(TEXT(A1,"000000000"),5,1)=0,--MID(TEXT(A1,"000000000"),6,1)=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000"),5,1)<>1,CHOOSE(MID(TEXT(A1,"000000000"),6,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(A1,"000000000"),6,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--MID(TEXT(A1,"000000000"),4,1)+MID(TEXT(A1,"000000000"),5,1)+MID(TEXT(A1,"000000000"),6,1))=0,,IF(OR((--MID(TEXT(A1,"000000000"),7,1)+MID(TEXT(A1,"000000000"),8,1)+RIGHT(TEXT(A1,"000000000")))=0,--MID(TEXT(A1,"000000000"),7,1)<>0)," Thousand "," Thousand and "))&CHOOSE(MID(TEXT(A1,"000000000"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(A1,"000000000"),7,1)=0,,IF(AND(--MID(TEXT(A1,"000000000"),8,1)=0,--RIGHT(TEXT(A1,"000000000"))=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000"),8,1)<>1,CHOOSE(RIGHT(TEXT(A1,"000000000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(RIGHT(TEXT(A1,"000000000"))+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
This awesome formula was developed by Pete M, a commercial manager/consultant, and was shared with the public by the Excel MVP Leila Gharani in her blog post.
Note: The formula assumes that you have the number in the cell A1. The formula will ignore numbers after decimal.
Chandoo’s Formula
Here is a similar formula created by the Excel MVP Chandoo that will convert Numbers into English Words. This formula uses the LET function which is currently available to Office 365 subscribers.
=LET(
th, INT(A1/1000),
th.h, MOD(th/100, 10),
th.t, MOD(th, 100),
th.tens1, INT(th.t/10),
th.tens2, MOD(th.t,10),
h,MOD(A1/100,10),
t, MOD(A1,100),
tens1, INT(t/10),
tens2, MOD(t,10),
tys, {"twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"},
upto19, {"one","two","three","four","five","six","seven","eight",
"nine","ten","eleven","twelve","thirteen","fourteen","fifteen",
"sixteen","seventeen","eighteen","nineteen"},
CONCAT(IF(th<1, "", IF(th.h>=1,INDEX(upto19,th.h)&" hundred ","") &
IF(th.t<1,"", IF(th.tens1<2,INDEX(upto19, th.t), INDEX(tys,th.tens1-1) & IF(th.tens2>=1,"-"&INDEX(upto19,th.tens2),"")))&
" thousand "),
IF(h>=1,INDEX(upto19,h)&" hundred ",""),
IF(t<1,"", IF(tens1<2,INDEX(upto19, t), INDEX(tys,tens1-1)& IF(tens2>=1,"-"&INDEX(upto19,tens2),"")))))
Chandoo, in his blog post has explained this formula in detail and has also done a video on the same.
Note: This formula works up to numbers 999,999 only and will ignore numbers after decimal.
Download the workbook
Excel workbook with Formulas to convert Numbers into Words
UDF to convert Numbers into Words
If you are comfortable using VBA, the following is a blog post that explains a User Defined Function in Excel that will convert Numbers into Words.
User Defined Function to convert Numbers in Words
