You might be looking a way to convert numbers into words in excel as excel does not have a default function for that so therefore, I will be explaining both methods (without VBA and with VBA) which you can use in order to convert numeric value into English words.
1. Convert Numbers into Words in Excel Without VBA
So let’s start with first one which is without VBA and for that I will be using a formula which is a bit lengthy but do not worry, you can get this formula from below button.
- Paste the formula where you wanna show value in words and replace the cell reference where your numeric value is. In my case I have the numeric value in Cell (B3) so I will replace the formula with my cell reference.
This might be your question that;
How to change multiple cell references in long formula?
By default this formula is applied on cell A1, so you can change the cell reference as per your data. You’ll need to refer cell which having numeric value many time in this formula and by doing this manually is time consuming, so you can use following shortcut to do that.
2. Remove equal (=) sign from beginning of formula, select the whole formula by pressing CTRL+A , click on replace, put existing cell reference in Find-what-box (in my case the cell reference is B2). Add new cell reference (having numeric value) in Replace-with-box (in my case the cell reference is B3) and click on replace all. So it will replace all the existing cell references (B2) into (B3).
Put back equal sign and hit the enter, so it will show the value in words. You can add formula on other values as well just by dragging down the same formula. (See below image).
As in above image the currency is showing in Dollars and you want to change the currency from Dollars to Ponds or any other currency, you can simply repeat the steps we did for changing the cell reference.
Simply remove the equal (=) sign, select the whole formula and replace dollars with ponds or any other currency you want, so it will show that specific currency instead of dollars. (You can see the result below).
2. Convert Numeric Value into Words in Excel With VBA
The best advantage of using VBA is that you don’t need to add the above formula each time you work on new workbook. For that all you need to do is to set the VBA code once and it will work on all of your different workbooks.
On the other hand there is a disadvantage as well which is that when you send that file to someone else, they will receive a security notice that there is a micro in this workbook and they won’t be able see amount in words unless the micro is enabled. I think this isn’t an issue if someone trust you, he/she can simply enable micro.
Anyways let’s move to the steps which you need to follow in order to convert value in words by using VBA.
Go to developer tab and open the visual basic or simply press ALT+F11
Click on insert and add module
Copy the VBA code and paste into newly added module (You can get this VBA code from Microsoft-Support-official-website or from below button)
Now just close the visual basic and write the below formula where you wanna get the value in words. (Also make sure you change the cell reference as per your sheet).
=SpellNumber(B3)
As you can see the result in the above image, you can simply drag the formula down to apply on other cells as well.
If you want to change the currency, just go back to visual basic by pressing ALT+F11 , select the VBA code and press CTRL+F , add currency you want and replace all with dollars.
When you’ll try to save this workbook, you’ll get the below error so simply click on no.
As you are running a VBA in this workbook so you cannot save this as a normal workbook.
You need to save this workbook as a Micro-Enabled workbook so then only it will work properly.
To avoid adding this VBA each time you work on different workbook, you can create Add-in which will run this VBA automatically when you open any excel workbook.
All you need to do is to select the type Excel Add-in and save it in the default folder.
In order to enable this Add-in, go to options, select Add-ins and click on go.
Choose the file name you saved and click ok so the Add-in will be enabled and will run across all of your workbooks.
So from now whenever you’ll open any of your excel workbook, you don’t need to add VBA code, only you need is to write this formula =spellnumber(cell reference) and rest that Add-in will do.
I hope you will find this article helpful. if you’re still facing any issue do let me know by commenting below and I’ll happy to assist you any further.
Thank you….!
Thank you so much for your effort!