Converting Amount to Words
Background

It is a common scenario that we require specified amount to be displayed in words. So we manually write it in words and forget to change it when respective amount in number changes leading to anomoly between amount in number and words. Accordingly various approaches or options to solve this problem have been discussed in this article :-

  1. Using in-built Excel functions
  2. Using Excel-BI add-in
  3. Using XML Webserive
  4. Using Macro
Using in-built Excel functions

Compatible Versions: Any version of Excel (works in Google Spreadsheet too)
Function Used: IF, MOD, INT, INDEX

Excel has awesome in-built handy functions which can solve very complex calculations. You may not even require macro for this. Just a proper and systematic approach may provide solution to it.

A small excel template (file) is displayed below which can convert any number to words. Sheet named logic contains actual implementation. Try changing amount to see the result.

You are free to download this file, or try it online. While integrating this functionality in your own file, please copy both sheets i.e. “demo” & “logic”. Link amount to be converted to cell E2 of “logic” sheet. In cell E17 of “logic” you get amount converted to words, which can be linked to the cell where you want amount in words.

Template does not support number bigger than 99,99,99,999. Also multiple numbers in a file cannot be converted by linking.
Using Excel-BI add-in

Compatible Versions: Office 2007, 2010, 2013, 2016, 2019 & Office 365
Function Used: BI.AMOUNTTOWORD

Developer/Administrator of Excel Kida website provides a Free of Cost add-in named Excel-BI. Information about add-in like how to download, install and use it is available on below website. On installing add-in, a new function named BI.AMOUNTTOWORD gets available.
https://erpcloudanalysis.in/excel-bi

excel bi add-in number to words formula demo
=BI.AMOUNTTOWORD(B2)
=BI.AMOUNTTOWORD(B2,1)

With prefix “Rupees” and suffix “Only”

Note: Excel-BI add-in needs to be installed on PC where this function is used. So, kindly make sure to copy/share this add-in if you email/transfer any excel file using this function.
Using XML Webservice

Compatible Versions: Office 2013, 2016, 2019 & Office 365
Functions Used: WEBSERVICE & FILTERXML

Excel has in-built function named WEBSERVICE which can fetch data from any URL which can return XML format data. Excel has another built-in function named FILTERXML which can extract data from XML. So using these 2 functions, we will convert number to words.

Excel Kida Website hosts publicly available webservice for such type of functionality free of cost. The number to be converted into words need to be specified in URL of webservice. Conversion from number to words is done on Excel Kida Web-Server and result is returned back to Excel.

excel webservice function amount to words demo

For example let's assume amount in number is written in cell B2. So the syntax for converting amount to words in cell B4 will be

=FILTERXML(WEBSERVICE("https://excelkida.com/xml/amount-to-word?style=ind&lang=en&num="&B2),"/data")
warning generated by excel when using webservice function
Excel might display above window if you paste directly in Excel. So, edit cell by pressing F2 & then paste it.

In many cases you might need to prefix “Rupees” and suffix “Only” to amount in words. So below expression can be used to achieve this

="Rupees "&FILTERXML(WEBSERVICE("https://excelkida.com/xml/amount-to-word?style=ind&lang=en&num="&B2),"/data")&" Only"

Excel Kida plans to implement support for converting amount to Hindi words in future.

Using Macro

Compatible Versions: Any version of Excel
Function Used: AMOUNTTOWORD

Macro is a Visual Basic programming solution to automate tasks in Excel. The macro file implementation for the above can be downloaded from below link
Amount to Words VBA Macro

To import macro into any Excel file go to Developer Tab and Click Visual Basic icon or press Alt + F11
excel developer ribbon screenshot vba
VBA Project window will open. Right Click Microsoft Excel Objects > Import File . Browse the amount-to-words.bas file downloaded from above link
excel vba project without module
After importing project window should display macro file (as shown below)
excel vba project with module
Close the Visual Basic Window. Now a new function named AMOUNTTOWORD becomes available. Use this function as shown below.

=AMOUNTTOWORD(B2)
Note : This macro needs to be imported to each Excel file where this function is to be used. Also, file needs to be saved in format Excel Macro Enabled Workbook. Each time this file is opened, macros need to be enabled manually.