Formula to convert number to text

nagapavan

Board Regular
Joined
Jan 23, 2008
Messages
54
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
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
if you need a formula solution, here we go with a simple one:p


Excel Workbook
CD
99456.56Rupees Nine Thousand Four Hundred Fifty Six And Paise Fifty Six only
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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","")
Excel Workbook
NameRefers To
O=Sheet1!$I$5:$AB$5
T=Sheet1!$I$7:$R$7
Workbook Defined Names



where the named ranges are
Excel Workbook
IJKLMNOPQRSTUVWXYZAAAB
5onetwothreefourfivesixseveneightNineteneleventwelvethirteenfourteenfifteensixteenseventeeneighteennineteen
Sheet1
Excel 2003
Excel Workbook
IJKLMNOPQR
7twentythirtyfortyfiftysixtyseventyeightyninety
Sheet1
Excel 2003
 
Upvote 0
[h=5]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.[/h]
 
Upvote 0
Having trouble separating the Ninety Nine, shows Ninetynine


Excel 2010
AB
19456.56Nine Thousand Nine Hundred Ninetynine And Ninetynine
Convert Amount to words
Cell Formulas
RangeFormula
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,"","")
Named Ranges
NameRefers ToCells
o='Convert Amount to words'!$I$5:$AB$5
T='Convert Amount to words'!$I$7:$R$7
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top