Results 1 to 6 of 6

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. #1
    New Member
    Join Date
    Jan 2008
    Posts
    49

    Default 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
    Last edited by nagapavan; Jul 29th, 2009 at 06:27 AM.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,629
    Microsoft MVP - Excel

  3. #3
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,319

    Default 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

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  4. #4
    New Member
    Join Date
    Sep 2011
    Posts
    4

    Default 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.


  5. #5
    New Member
    Join Date
    Sep 2011
    Posts
    4

    Default Re: Formula to convert number to text


  6. #6
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,467

    Default 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


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com