Converting Numbers to Words using Excel 2007

There is no provision or tool available in excel 2007 for converting Numbers to words. This is possible only with the VBA code. But with the combination of some simple available formulae in excel 2007, it is possible to create a tool for converting Numbers to words without any complicated coding. This process needs creation of some table of contents having some regular excel formulae. This table can be kept invisible by keeping it in separate worksheet and hiding it. Only result can be used in your working sheet. The table which is to be created will be as shown below.

Let us go to step by step procedure how to create it.

Create a table as shown below. You have to enter direct values in Red colour cells. No formulae are existing in Red colour cells. But all Green colour cells are having specific formulae in it. In the extreme left side, values from 1 to 17 are visible, but you have to create it up to 99. Enter the formulae in the corresponding Green cells from the below given formulae.

Cell–E18 : =IF(H15=0,””,VLOOKUP(H15,$B$2:$C$101,2))

Cell—E17: =IF(F8=0,””,VLOOKUP(F8,$B$2:$C$101,2))

Cell—E16: =IF(G9=0,””,VLOOKUP(G9,$B$2:$C$101,2)&” Hundred “)

Cell—E15: =IF(G10=0,””,VLOOKUP(G10,$B$2:$C$101,2)&” Thousand “)

Cell—E14: =IF(G11=0,””,VLOOKUP(G11,$B$2:$C$101,2)&” Lakh “)

Cell—E13: =IF(G12=0,””,VLOOKUP(G12,$B$2:$C$101,2)&” Crore “)

Cell—H13: =IF(F2<1000000000,ROUNDDOWN(F2,0),0)

Cell—H14: =F2-H13

Cell—H15: =ROUND(H14*100,0)

Cell—E8: =ROUNDDOWN(H13,-2)

Cell—E9: =ROUNDDOWN(H13,-3)

Cell—E10: =ROUNDDOWN(H13,-5)

Cell—E11: =ROUNDDOWN(H13,-7)

Cell—E12: =ROUNDDOWN(H13,-9)

Cell—F8: =+H13-E8

Cell—F9: =+H13-E9-F8

Cell—F10: =+H13-E10-F9-F8

Cell—F11: =H13-E11-F10-F9-F8

Cell—F12: =H13-E12-F11-F10-F9-F8

Cell—G9: =ROUND(F9/100,0)

Cell—G10: =ROUND(F10/1000,0)

Cell—G11: =ROUND(F11/100000,0)

Cell—G12: =ROUND(F12/10000000,0)

Finally in the answer cell i.e in F4 the formula will be

=IF(F2=0,””,IF(H14=0,H16&E13&E14&E15&E16&E17&H18,H16&E13&E14&E15&E16&E17&H17&E18&H18)).

Try to create formula by using above simple combination of formulae. The result can be as shown below.

In this case, the created table is in another hidden work sheet. Only links have been given to that formula.

Try it.If you are unable to get it, Click Here to download a developed table .

8 Responses to Converting Numbers to Words using Excel 2007

  1. Ramana Reddy says:

    Dear reddy sir,

    yes, given formula’s are worked out. could you please tell me about how to link with hidden work sheet to working sheet. I mean how to hide the work sheet.

    Thanking you sir,
    Ramana reddy.

  2. k venkata ramesh says:

    Dear reddy sir,
    i am not really used to these formulas. i have tried to download the excel sheet. I have a problem with that. i am not using it for currency. i want to have an excel sheet which can convert decimals also
    Eg: 76.50 (Seventy six and half only). will you please help me in this regard

    • admin says:

      Try to download developed spreadsheet by clicking “click here ” at the end of article. Which gives you conversion of decimals also or you can edit the formula as per your requirement

  3. enyo says:

    how can i replicate this formula in multiple cells

  4. amit bhatnagar says:

    Of great use,
    Could you please tell more about how to develop our own work sheet ??
    Thanks sir

    • Pratap Reddy says:

      Download the developed worksheet from the link given at the end of article and edit as per your requirement

  5. B Bera says:

    Very helpful to those who are not trained in an institution.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>