# Formula to convert number to text

This is a discussion on Formula to convert number to text within the Excel Questions forums, part of the Question Forums category; Hi there, Is there a formula to convert number to text in excel? I am NOT looking for a VBA ...

1. ## Formula to convert number to text

Hi there,

Is there a formula to convert number to text in excel? I am NOT looking for a VBA code. I am seeing if there could be a formula.

That is if A1 has 500
B1 should show Five Hundred

Any help is appreciated.

Pavan

3. ## Re: Formula to convert number to text

if you need a formula solution, here we go with a simple one

Sheet1
CD
99456.56Rupees Nine Thousand Four Hundred Fifty Six And Paise Fifty Six only
Excel 2003

Worksheet Formulas
CellFormula
D9=PROPER(TRIM(IF(C9>=1,"Rupees ","")&IF(INT(C9/10^7)>19,INDEX(T,INT(C9/10^8)+1)&INDEX(O,MOD(INT(C9/10^7),10)+1),INDEX(O,INT(C9/10^7)+1))&IF(INT(C9/10^7)>0," crore "," ")&IF(INT(MOD(C9,10^7)/10^5)>19,INDEX(T,INT(MOD(C9,10^7)/10^6)+1)&INDEX(O,MOD(INT(MOD(C9,10^7)/10^5),10)+1),INDEX(O,INT(MOD(C9,10^7)/10^5)+1))&IF(INT(MOD(C9,10^7)/10^5)>0," lakh "," ")&IF(INT(MOD(C9,10^5)/10^3)>19,INDEX(T,INT(MOD(C9,10^5)/10^4)+1)&INDEX(O,MOD(INT(MOD(C9,10^5)/10^3),10)+1),INDEX(O,INT(MOD(C9,10^5)/10^3)+1))&IF(INT(MOD(C9,10^5)/10^3)>0," thousand "," ")&IF(INT(MOD(C9,10^3)/100)>19,INDEX(T,INT(MOD(C9,10^3)/10^3+1))&INDEX(O,INT(MOD(C9,10^3)/100)+1),INDEX(O,INT(MOD(C9,10^3)/100)+1))&IF(INT(MOD(C9,10^3)/100)>0," hundred "," ")&IF(MOD(C9,100)>19,INDEX(T,INT(MOD(C9,100)/10)+1)&INDEX(O,MOD(C9,10)+1),INDEX(O,MOD(C9,100)+1))&IF(MOD(C9,1)>0,IF(C9>=1," and ","")&" Paise ","")&IF(MOD(C9,1)>0.19,INDEX(T,INT(MOD(C9,1)*10)+1)&INDEX(O,MOD(MOD(C9,1)*100,10)+1),INDEX(O,INT(MOD(C9,1)*100)+1))))&IF(C9>0," only","")

Workbook Defined Names
NameRefers To
O=Sheet1!\$I\$5:\$AB\$5
T=Sheet1!\$I\$7:\$R\$7

where the named ranges are
Sheet1
IJKLMNOPQRSTUVWXYZAAAB
5 one two three four five six seven eight Nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen
Excel 2003

Sheet1
IJKLMNOPQR
7 twenty thirty forty fifty sixtyseventy eighty ninety
Excel 2003

4. ## Re: Formula to convert number to text

EXCEL FORMULA CHALLENGE:Needs a formula like as above , expand the formula to generate from One to Nine Hundred Ninety Nine Thousand Nine Hundred & Ninety Nine? 1 to 999,999,999 all numbers into words between this range ?? possible ? only needs a formula please help.

6. ## Re: Formula to convert number to text

Having trouble separating the Ninety Nine, shows Ninetynine

Excel 2010
AB
19456.56Nine Thousand Nine Hundred Ninetynine And Ninetynine

Convert Amount to words

Worksheet Formulas
CellFormula
B1=PROPER(TRIM(IF(C9>=1,"","")&IF(INT(C9/10^7)>19,INDEX(T,INT(C9/10^8)+1)&INDEX(o,MOD(INT(C9/10^7),10)+1),INDEX(o,INT(C9/10^7)+1))&IF(INT(C9/10^7)>0," crore "," ")&IF(INT(MOD(C9,10^7)/10^5)>19,INDEX(T,INT(MOD(C9,10^7)/10^6)+1)&INDEX(o,MOD(INT(MOD(C9,10^7)/10^5),10)+1),INDEX(o,INT(MOD(C9,10^7)/10^5)+1))&IF(INT(MOD(C9,10^7)/10^5)>0," lakh "," ")&IF(INT(MOD(C9,10^5)/10^3)>19,INDEX(T,INT(MOD(C9,10^5)/10^4)+1)&INDEX(o,MOD(INT(MOD(C9,10^5)/10^3),10)+1),INDEX(o,INT(MOD(C9,10^5)/10^3)+1))&IF(INT(MOD(C9,10^5)/10^3)>0," thousand "," ")&IF(INT(MOD(C9,10^3)/100)>19,INDEX(T,INT(MOD(C9,10^3)/10^3+1))&INDEX(o,INT(MOD(C9,10^3)/100)+1),INDEX(o,INT(MOD(C9,10^3)/100)+1))&IF(INT(MOD(C9,10^3)/100)>0," hundred "," ")&IF(MOD(C9,100)>19,INDEX(T,INT(MOD(C9,100)/10)+1)&INDEX(o,MOD(C9,10)+1),INDEX(o,MOD(C9,100)+1))&IF(MOD(C9,1)>0,IF(C9>=1," and ","")&" ","")&IF(MOD(C9,1)>0.19,INDEX(T,INT(MOD(C9,1)*10)+1)&INDEX(o,MOD(MOD(C9,1)*100,10)+1),INDEX(o,INT(MOD(C9,1)*100)+1))))&IF(C9>0,"","")

Workbook Defined Names
NameRefers To
o='Convert Amount to words'!\$I\$5:\$AB\$5
T='Convert Amount to words'!\$I\$7:\$R\$7

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•