We get requests now and again asking how to convert numbers to words (or convert currency) e.g. 123.45 becomes One Hundred Twenty Three Dollars and Forty Five Cents. Excel doesn’t provide a function to do this so I’m going to write my own.
Microsoft does provide some VBA code which I will use as my starting point, and I have seen other examples, but these examples only allow for one currency.
My code allows you to specify what currency you use, and it takes into account regional variations of the decimal separator. Some countries use . (period or full stop) and some use , (comma).
I've created two workbooks that you can use. The first is for use with currency. The second is for general use and will convert the number to a string of words. You can then join that string with whatever you want.
For example, 23,482 could be converted to Twenty Three Thousand Four Hundred Eighty Two people live in this town.
By joining a string like " people live in this town." to the output from the conversion function, you can use the function however you want.
User Defined Function
The code is written as a User Defined Function or UDF. This is just like a regular Excel function that you might use like SUM() and is used in a similar way.
The function is called NumToWords() and you use it in a worksheet by passing a number, or a number stored as text, to it
or you can use it by referencing another cell like this :
The function will take either a string (text) or a number as input and it returns a string (text).
Excel's Number Limitation
Excel only displays a maximum of 15 digits for a number typed into a cell. Any digits after that are changed to 0.
This is a problem if you are working with things like credit card numbers which are typically 16 digits long. You'll need to use a nifty formula explained in this custom cell format post to solve this particular problem.
This limitation is also an issue if you are working with numbers in the 10's or 100's of trillions. Let's say you want to work out the world's gross domestic product (GDP) for the last 10 years, which will be into the 100's of trillions.
What you have to do is store the number as text. Doing this will allow you to work with numbers up to 999,999,999,999,999.99 which is the maximum currently supported by this function.
If you are not using a dollar as your currency as we do here in Australia, then you can change the currency to whatever you want. Note : this code doesn’t distinguish between countries so you can leave the settings at dollar if you live in the USA, Canada or anywhere else using dollar.
At the top of the code there’s a section where you type in the name of your currency unit and the name of your subunit. For example
Sub Units of currency
You may have noticed that I’ve allowed for a special case where the name for a single subunit is different to more than one, as is the case with Pounds Sterling.
One subunit is called a penny, more than one would be referred to as pence. So £10.01 would be Ten Pounds and One Penny but £10.02 would be Ten Pounds and Two Pence. This works in a similar way for 2 cents or just 1 cent.
If you use the same term for one and more than one of your subunit then just change the code to reflect this.
If you use a , (comma) as your decimal separator then in the same area of code shown above, change the character used for the DecimalSeparator variable.
Update 1 - Spanish Translation
I received a translation of this code into Spanish from Pablo Baez. He said he wanted to share it with his brother in South America, so here it is - thanks Pablo.
The name of the function in Spanish is NumEnPalabras()
If you have translated the code into other languages, or would like some assistance to do so, please get in touch
Update 2 - Whole Numbers
I also received a request from Khaled Naser Almarwa in Saudi Arabia, asking if the code could be altered to output only whole numbers. Yes it can and you can get this new code here.
This function is called NumToWholeWords()
Update 3 - Fractional Sub Units
Chastine asked if the output could be of the form :
One Thousand Two Hundred Dollars and 50/100 only
Yes it can and the new function for this is called NumToWordsFrac()