Here is a new year gift for the Indian customers. Converting Amount to Words is always a problem. Many application require it and that code is always written manually, within the application. What is really needed is a comprehensive Excel function. Many people have written lots of Excel functions. But neither of them is specific to India, nor is it comprehensive.
We (Raj and me) had created it in 2005, but I had forgotten to publish it on the blog. We have done good amount of testing – but still you have to use it at your own risk. No guarantees. Read on to download, install and use the Excel function.
Amount to Words installation instructions
This macro will work on Excel 2010 onwards.
- Download the Amount to Words macro file XLSM as a ZIP format from here.
- Unzip it in a new directory (folder).
- You will see a file called Amount2Words2017 V8.xlsm
- Double click to open the file.
- You may see the Enable Macros dialog. Choose Enable.
- DO NOT use the file as it is. It will NOT work.
- You must save it as an add-in first.
- Choose File – Save As and choose the type XLAM
- The path will automatically change to the appropriate Add-in folder.
- DO NOT change the path.
- Once the file is saved, close the XLSM file. You don’t need it any longer.
- Now go to File – Options dialog.
- Choose Add-ins option.
- At the bottom you will see the Excel Add-ins dropdown.
- Choose the Go.. button.
- Now you will see a list of Excel Add-ins.
- Select or activate the checkbox showing Amount to Words Function
- Click OK
- The add-in is installed.
- Just to be on the safer side, close and re-open Excel
- Now the Amount to Words functionality is available across all Excel files in your login (profile)
Why did I not provide the XLAM file directly?
Because, many users make a mistake while storing the add-in in the right place.
The method shown above ensures that it is saved in the right place.
Developers can use the VBA code
While the XLSM file is open, you can press Alt-F11 to view the code and reuse it if you like. Please give a link to my blog as attribution. Thanks.
How to use Amount to Words Add-in
- Open any Excel file where you want to convert Amount to Words.
- Click on any empty cell.
- Notice that you now have a new tab in ribbon called Efficiency 365.
- Open that tab.
- The function is called AmountToWords.
- The function accepts 16 parameters.
- It is difficult to type all the parameters correctly.
- Therefore, we have provided you with a very friendly user interface (dialog)
- Click on the Generate Function button.
- Type a sample number and choose the exact parameters you want.
- When you get exactly what you want, click on the Insert into current cell button.
- Remember that whatever is currently there in that cell will be OVERWRITTEN. UNDO may not work.
- Now press F2 to copy that syntax and paste it wherever you want it.
- Change the sample number to the cell reference where the actual number is.
- If you type this in a Table, the formula will be auto-copied.
- You can now reuse the formula anywhere you want.
- Anytime you want to change the output, choose Generate Function button again and create new syntax.
Create the Amount to Words function syntax interactively
Here is the dialog you see when you click on the Generate Function button in the Efficiency 365 tab. Make sure that the current cell is empty before invoking the dialog. It does not pick up the value from the current cell. If the current cell is already populated, it will overwrite that value or formula. Be careful.
- The dialog is just a sample syntax generator.
- It does not pickup values from the current cell.
- Type a sample value in the Amount textbox.
- Now change the parameters exactly as per your specific needs.
- When you change any parameter, the resulting function will be shown, along with the actual output.
- Once you get exactly the output you want, choose the Insert into current cell button. This button is inactive till you type some value in the Amount textbox.
- Close the dialog.
- Now press F2 to edit the sample function.
- Copy the syntax and paste it where you have the actual numbers which you want to convert to words.
- Change the first parameter to the cell which has the actual numbers.
- Now you will get your number converted as per the desired output syntax
- If needed copy that formula to cover more cells. If you copy pasted the formula in a Table, the new formula will be auto-copied
- Practice it a couple of times and you are good to go!
Amount to Words function syntax
The Amount to Words function accepts 16 different parameters. Why so many parameters? Because we wanted to provide all possible flexibility in the conversion process. Those who need this function will be able to appreciate the thought and the comprehensive set of parameters we have provided. If you need more, let us know (post a comment here) and we will work on your wish-list.
Here is a detailed description of what each parameter does. You can directly create a function by understanding the parameters. Initially however, you should use the Generate Function button in the Efficiency 365 tab to create function syntax interactively.
|1||The value which is to be converted to text (Mandatory parameter)||Cell Reference||1435.56||=AmountToWords(E8)||Rupees one thousand, four hundred and thirty five and fifty six paisa only|
|2||Whether to put commas in places||0 or 1||1435.56||=AmountToWords(E9,FALSE)||Rupees one thousand four hundred and thirty five and fifty six paisa only|
|3||Should the word “Only” be added at the end of the output||0 or 1||1435.56||=AmountToWords(E10,,FALSE)||Rupees one thousand, four hundred and thirty five and fifty six paisa|
|4||Specify the word Rupees or not||0 or 1||1435.56||=AmountToWords(E11,,,FALSE)||One thousand, four hundred and thirty five and fifty six paisa only|
|5||Whether to show the paisa component||0 or 1||1435.56||=AmountToWords(E12,,,,FALSE)||Rupees one thousand, four hundred and thirty five only|
|6||Whether to put the word “and”||0 or 1||1435.56||=AmountToWords(E13,,,,,0)||Rupees one thousand, four hundred thirty five and fifty six paisa only|
|7||If true, the word for Rupees will come AFTER the description of the rupee amount||0 or 1||1435.56||=AmountToWords(E14,,,,,,1)||One thousand, four hundred and thirty five rupees and fifty six paisa only|
|8||If true, the word for Paisa will come AFTER the description of the Paisa amount||0 or 1||1435.56||=AmountToWords(E15,,,,,,,1)||Rupees one thousand, four hundred and thirty five and fifty six paisa only|
|9||U pper||“U”||1435.56||=AmountToWords(E16,,,,,,,,”U”)||RUPEES ONE THOUSAND, FOUR HUNDRED AND THIRTY FIVE AND FIFTY SIX PAISA ONLY|
|10||L ower||“L”||1435.56||=AmountToWords(E17,,,,,,,,”L”)||rupees one thousand, four hundred and thirty five and fifty six paisa only|
|11||T itle– and should always be lower case||“T”||1435.56||=AmountToWords(E18,,,,,,,,”T”)||Rupees One Thousand, Four Hundred and Thirty Five and Fifty Six Paisa Only|
|12||S entence – default||“S”||1435.56||=AmountToWords(E19,,,,,,,,”S”)||Rupees one thousand, four hundred and thirty five and fifty six paisa only|
|13||What to specify in the “Rupee” position (Position 1)||“Rs”||1435.56||=AmountToWords(E20,,,,,,,,,”Rs”)||Rs one thousand, four hundred and thirty five and fifty six paisa only|
|14||What to specify in position 18, in case ShowPaisa is True||“P”||1435.56||=AmountToWords(E21,,,,,,,,,,”P”)||Rupees one thousand, four hundred and thirty five and fifty six p only|
|15||What to call Lacs?||“Lakhs”||123456.56||=AmountToWords(E22,,,,,,,,,,,”Lakhs”)||Rupees one lakhs, twenty three thousand, four hundred and fifty six and fifty six paisa only|
|16||What to output if ShowPaisa is TRUE but paisa value is zero?||“No”||1435||=AmountToWords(E23,,,,,,,,,,,,”No”)||Rupees one thousand, four hundred and thirty five and no paisa only|
Practical usage of Amount to Words Macro
This function is designed for Indian accounting system only.
This functionality is typically required for for cheque printing.
However, any scenario where Amount to Words conversion is required, this function is useful.
My friend Raj wrote the code.
Share the Amount to Words function
If you like the function, please share it with your colleagues and friends. They will surely thank you for solving a long-standing problem.
Feedback or wish-list
Your feedback is most welcome. Please post it as a comment here.
We are not perfect. If we have missed some functionality, do let us know.