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 ...
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 by nagapavan; Jul 29th, 2009 at 06:27 AM.
Pavan Kumar Venuturupalli
Microsoft MVP - Excel
if you need a formula solution, here we go with a simple one
Sheet1Excel 2003
C D 9 9456.56 Rupees Nine Thousand Four Hundred Fifty Six And Paise Fifty Six only
Worksheet Formulas
Cell Formula 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
Name Refers To O =Sheet1!$I$5:$AB$5 T =Sheet1!$I$7:$R$7
where the named ranges are
Sheet1Excel 2003
I J K L M N O P Q R S T U V W X Y Z AA AB 5 one two three four five six seven eight Nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen
Sheet1Excel 2003
I J K L M N O P Q R 7 twenty thirty forty fifty sixty seventy eighty ninety
regards
Sankar
show your sheet on the board, get HTML maker
யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா
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.
Having trouble separating the Ninety Nine, shows Ninetynine
Excel 2010
A B 1 9456.56 Nine Thousand Nine Hundred Ninetynine And Ninetynine Convert Amount to words
Worksheet Formulas
Cell Formula 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
Name Refers To o ='Convert Amount to words'!$I$5:$AB$5 T ='Convert Amount to words'!$I$7:$R$7
Like this thread? Share it with others