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.
Download the Code
Sample workbooks with working VBA code can be downloaded from the bottom of this post - just scroll down to the Download Section.
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.
Other Currencies
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.
Decimal Separator
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()
Download the Sample Workbooks
Enter your email address below to download the workbook with the data and code from this post.
Curse You Excel Cursor
Kristel
Hi I’m getting hard time to becuase the amount of decimal point was not rounded off how can I edit the code for this?
thanks
Philip Treacy
Hi Kristel,
Do the rounding in Excel. Wrap the function in one of the rounding functions e.g.
=NumToWords(ROUND())
Regards
Phil
Kristel
I’m using spell number code in VBA
i need to get the round off number in the decimal point e.g
356.8795 = Three Hundred Fifty Six & 87/100 only
and it should be like this
356.8795 = Three Hundred Fifty Six & 88/100 only
Philip Treacy
Hi Kristel,
Use ROUNDUP
=numtowordsfrac(ROUNDUP(A1,2))
where your number is in cell A1
Regards
Phil
Sarfaraz
Hi.
My Currency is Bahrain Dinars.
Example: 125.425 = One hundred Twenty Five Dinars And Four Hundred Twenty Five Fils Only.
Can you please give me this code
Catalin Bombea
Hi Sarfaraz,
You have to dive into the code and replace the unit/subunit names, you have instructions in the article. We do not know how to translate all necessary parameters.
Please review the instructions. Use our forum to upload your translations, we will help you implement them.
Vincent.Wu
Hi, Catalin Bombea,
I use your code and changed only the currency to Pounds and Piastres, but after use the NumToWords() Function, it only shows =#Names?
I don’t know what it should be like. My Excel version is Excel 2016.
Catalin Bombea
Hi Vincent,
Can you upload on our forum the file with your changes to see what’s wrong?
Cheapros
Hi
i would like to translate from english to Khmer. Can you help me?
Catalin Bombea
Hi,
Please start a new topic on our forum, we can help you there. You will be able to upload a sample file with your code, just create a new topic after sign-up.
Arnas
Hey, thanks a lot for the fractions formula. But could you help me to edit the Fractions formula so a number like 10,01 EUR is showed as 1/100, not 01/100? I just cannot figure out how to edit the code.
Catalin Bombea
Hi Arnas,
Replace this code:
SubUnits = ” and ” & SubUnits & SubUnitName
with:
SubUnits = ” and ” & Val(SubUnits) & SubUnitName
Sheilla H. Mallorca
Hi Sir Philip,
I’m trying to copy your formulas but all is unsuccessful, can you give me the formula or option explicit on how to configure amount in words in Philippine Currency?
Example: 26,114.73= Twenty Six Thousand One Hundred Fourteen and 73/100 Only
10,000.00= Ten Thousand Pesos Only
Hope to receive your response the soonest.
Thank you very much
Catalin Bombea
Hi Sheilla,
Try the fractional version, you can find it here.
Of course, you have to edit the units and sub units names.
RICO SENIS CERENO
this working thank you very much
Catalin Bombea
You’re welcome, nice to hear you managed to make it work
Chris Nelson
I like this function very much. I’ve added slightly to it so that it will properly (grammatically) include necessary hyphens where they are required. See if you agree:
…
ElseIf Val(TensText) Mod 10 = 0 Then ‘ If value is 20, 30, 40 … up to 90…
Select Case Val(TensText)
Case 20: Result = “Twenty ”
Case 30: Result = “Thirty ”
Case 40: Result = “Forty ”
Case 50: Result = “Fifty ”
Case 60: Result = “Sixty ”
Case 70: Result = “Seventy ”
Case 80: Result = “Eighty ”
Case 90: Result = “Ninety ”
Case Else
End Select
Else ‘ If value between 21-99, exclusive of the break points above…
Select Case Val(Left(TensText, 1))
Case 2: Result = “Twenty-”
Case 3: Result = “Thirty-”
Case 4: Result = “Forty-”
Case 5: Result = “Fifty-”
Case 6: Result = “Sixty-”
Case 7: Result = “Seventy-”
Case 8: Result = “Eighty-”
Case 9: Result = “Ninety-”
Case Else
End Select
Philip Treacy
Thanks Chris. If the hyphens work for your particular situation then great.
Regards
Phil
Stephanie
Do you have a code that doesn’t insert Dollars and Cents. I need to do a project that has to do with shares not currency so i just need whole numbers converted to words.
Thanks
Stephanie
I actually found one of your files and deleted what i needed to make this happen. Thanks!
UEMSH
Dear sir please
let us know the programme for after decimal three digit
e.g.100.123
after decimal one hundred twenty three baisa only
Philip Treacy
Hi,
This has already been answered a couple of times in the comments – see this one
https://www.myonlinetraininghub.com/convert-numbers-currency-to-words-with-excel-vba#comment-40205
Regards
Phil
A Ray
Can you please make a code for Indian Rupees in Whole numbers? Please note that Trillion, Million and Billion is not used in Indian Currency system. Comma for separation is also used in a different manner. For example the number 1234567899000 in Rupees is written as ₹ 123456,78,99,000 in short and Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six Crore Seventy Eight Lakh Ninety Nine Thousand. Use of the word ‘Only’ at the end has become obsolete now-a-days.
Can you please post a code which converts ₹ 123456,78,99,000 as Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six Crore Seventy Eight Lakh Ninety Nine Thousand in words.
Thanks in advance and Regards.
Catalin Bombea
Hi,
Can you please post a new topic on our forum for this? I guess you followed the instructions from this article for rupee, and you updated the millions, billions section of the code with your specific text:
Indian Rupee
‘ Change these as required **************************
UnitName = “Rupee” ‘ NOTE : This is singular
SubUnitName = “Paisa” ‘ NOTE : This is plural
SubUnitSingularName = “Paisa” ‘ NOTE : This is singular
DecimalSeparator = “.”
‘ ***************************************************
A Ray
I have tried this following the instructions given in this post but it did not meet my purpose. I have created a topic on this in the Forum which now awaits moderation.
Pratik Karani
Sir,
I too require in ruppes as mentioned by Ray, i followed all steps given in forum below, but it does not yield results.
Can you help us with indian version
Catalin Bombea
Hi Pratik,
See this post started by Ray on our forum.
Ayush Ruel Das
Can you please tell me how to use the same code if I want the text in hindi.
Philip Treacy
Sorry, I don’t speak Hindi so I don’t know how to translate it.
SUBHASIS DE
numtowords not work in my excel
Catalin Bombea
Hi,
Make sure you allow macros to run.
houcine
I need to convert a number with three digits after the decimal point, can you help me?
Catalin Bombea
Hi,
Please use our forum, you will be able to upload there a sample file. Make sure you prepare all details for your currency.
Emiko
Hi, I have tried to modify some of the codes and I did it.
But, still have one question.
How to make like this:
0.92 –> Ninety Two Cents
Currently, it shows up as “And Ninety Two Cents”. How to throw away the “And” at the front if there is none in the Units?
Catalin Bombea
Hi Emiko,
What’s wrong with the original version? The result is: No Dollars and Ninety Two Cents.
We don’t know what changes you made to advice, but you can try again the original version, adjusting the cell formula instead:
=Substitute(NumToWords(A1),”No Dollars and “,””)
joy
Can you make Dirhams
Catalin Bombea
Hi Joy,
What do you mean? Forge them? (just kidding)
Please use our forum to upload your currency details, I do not know those details.
Have you tried to follow the instructions provided in this article, to change the currency?
chixie
hi, thanks a lot for this..this is a big help.but i got one problem..why is my “cents”not spelled?i mean when i typed in (180.50) it appeared one hundred eighty and no cents…what seems to be the problem here?
Catalin Bombea
Hi,
I tested the sample file available for download, and here is what I got:
180,5 Eighteen Thousand Dollars and No Cents
180.50 One Hundred Eighty Dollars and Fifty Cents
If you have “.” as decimal separator and you type a comma instead of a dot, the result will have “No Cents”
If this is not the case, I’m not seeing any other reason for that result you mentioned, you will have to provide your sample file where we can see that result, to check it. Use our forum for upload.
Bland
Dear Catalin thank you for your help. i inserted the code you provided to me under “Change these as required”. but i faced some errors. Is it possible to provide me the full code for this module. thanks in advanced.
Catalin Bombea
Hi Bland,
I don’t have other code than the code provided in this article. As mentioned before, if you need personaliation, start a new topic on our forum where we can upload and test files.
See you there.
Cheers,
Catalin
Bland
hello can we add multiple currencies to this module example Dollars, Euro, pound, … thanks
Catalin Bombea
Hi,
Yes, you can copy that module and create different versions, there are already 3 examples for Pounds, Yes, Rupee, and a spanish translation. You can add a second argument to specify the currency:
=NumToWords(A1,”USD”)
For this, you have to add conditional logic:
‘defaults:
UnitName = “Dollar”
SubUnitName = “Cents”
SubUnitSingularName = “Cent”
DecimalSeparator = “.”
ReDim Place(9) As String
Place(2) = ” Thousand “: Place(3) = ” Million “: Place(4) = ” Billion “: Place(5) = ” Trillion ”
If Currency=”Pound” then
UnitName = “Pound”: SubUnitName = “Pence”: SubUnitSingularName = “Penny”: DecimalSeparator = “.”
Place(2) = ” Thousand “: Place(3) = ” Million “: Place(4) = ” Billion “: Place(5) = ” Trillion ”
End IF
If Currency=”Yen” then
UnitName = “Yen”: SubUnitName = “Sen”: SubUnitSingularName = “Sen”: DecimalSeparator = “.”
Place(2) = ” Thousand “: Place(3) = ” Million “: Place(4) = ” Billion “: Place(5) = ” Trillion ” ‘change if needed
End IF
If Currency=”Rupee” then
UnitName = “Rupee”: SubUnitName = “Paisa”: SubUnitSingularName = “Paisa”: DecimalSeparator = “.”
Place(2) = ” Thousand “: Place(3) = ” Million “: Place(4) = ” Billion “: Place(5) = ” Trillion ” ‘change if needed
End IF
In the same way, using a Select Case, you might need to translate the GetHundreds, GetTens, GetDigit functions.
If you’re interested, you can open a new topic on our forum, where our members can help building such modifications.
shiva
Hi,
I am trying to convert the numbers in words into lakh.
123455.10 For example(One Lakh Twenty-Three Thousand and Fifty-Five Dollars and Ten Cents)
But I am not able to convert exactly what I want. It is Converting into (One Hundred and Twenty Three and Four Hundred and Fifty-Five)
Please help
Catalin Bombea
Hi Shiva,
Try posting some examples and your code on our forum, we will try to help you.
Cheers,
Catalin
Richards
hello, your article has been very educating. How can i adapt this to Naira & kobo?
Philip Treacy
Hi,
Change the UnitName, SubUnitName and SubUnitSingularName to Naira, Kobos and Kobo.
Regards
Phil
KETAN JOSHI
SUPERB
Philip Treacy
Thanks Ketan
Belle Touse
Hello Philip,
The word conversion is sometimes resulting in a false number. This is only happening in the cents section and only on a particular worksheet, and it happened 4 out of 12 times per worksheet. For example, $366.13 is resulting in Three Hundred Sixty-Six Dollars and Twelve Cents. Can you please assist? Thanks.
Philip Treacy
hi Belle,
Can you please supply your workbook? You can open a Forum topic and attach the file there.
Thanks
Phil
Chris Nelson
I’m guessing that’s going to be due to a “failure to round” error in the value being examined. That is, the original value is, let’s say, 1.125, which the function “sees” as “one dollar plus twelve cents”, ignoring what follows the second decimal place. My immediate suggestion would be to examine the formula that creates the value to be converted to be certain that it “ends” at the second decimal point. In other words: =Round( value, 2) and see if the NumToWords function then produces the expected “one dollar and thirteen cents”.
Nilesh
Hi,
How to put Currency name in beginning , in sentence.
Example of $135.20
Dollars One Hundred Thirty Five, and Cents 20 Only.
Philip Treacy
Replace the last two Select statements with this
Select Case Units Case "" Units = "No " & UnitName & "s" Case "One" Units = UnitName & " One " Case Else Units = UnitName & "s " & Units End Select Select Case SubUnits Case "" SubUnits = " and No " & SubUnitName Case "One" SubUnits = " " & SubUnitSingularName & " and One " Case Else SubUnits = " and " & SubUnitName & " " & SubUnits End Selectand if you want “Only” at the end, replace the last line with this
Regards
Phil
Nilesh
Thanks Philips,
Please put full formula, with above changes.
Regards
Nilesh
Philip Treacy
Hi Nilesh,
You just need to copy/paste the code I provided. Replace the last 2 Select statements. Search fro the code that starts ‘Select Case Units’. That’s whereto start.
Regards
Phil
Nilesh
Hey Phil,
Thanks a lot, Its Done.
Appreciate your help.
Cheers!
Nilesh
Philip Treacy
You’re welcome.
Vieng
Excuse me, may i ask why it can not convert to Lao Language for this code when i code in lao language it shows ???? like this. Do u have any suggestions?
I look forward to hearing from you
Thank you
Catalin Bombea
Hi Vieng,
Can you upload what you did on our forum(create a new topic after sign-up)? We cannot replicate what you are saying.
Thank you
Syed
I have a Purchase Order Worksheet in which I have to deal with Multiple currencies. I do have a drop down list of Currencies & Spell Number Addin for conversion from number to words but it does not have the currency name and subunit name only whole numbers
I need a macro so that if i select a currency Type from my drop down list then it should convert to text with the corresponding currency name and subunit name in words.
Hope to get an early reply from you
Catalin Bombea
Hi Syed,
Please upload a sample file with your structure on our forum, it’s the best way to get an answer for your problem. Open a new topic after sign-in.
Catalin
Ken
Might be easier to use an IF statement
for instance if B4 is the cell that has the currency identy and B5 is the number you want to convert then you can do something like this:
like =IF(B4=”Dollars”,NumToWords(B5),IF(B4=”Yen”,NumToYen(B5)… etc…
You would have to trite a separate macro for each currency, maybe there is an easier way to do it?
Catalin Bombea
Hi Ken,
There can be many ways to do that. For example, all currency related parameters can be stored into a dictionary, using the currency name as the key, this way many currencies can be used in the same code.
The function can take the currency name as an argument, like: NumToWords(B5, “Yen”), or NumToWords(B5, “USD”), or just NumToWords(B5, B4), if the B4 cell contains the currency name, no need for nested IF’s.
Catalin
Syed
Please show me few ways how can that be done. It seems Ken is correct that we have to write separate macros for each of the currencies with corresponding Unit & Sub Unit names. But I don’t know how to make that macro work depending upon my currency selection.
Help is really appreciated
Catalin Bombea
Hi Syed,
You can do this: Make sure that the function has 2 arguments, currency as the second argument:
Function NumToWords(ByVal MyNumber, Optional Byval Curr as String=”USD”) ‘if the currency is not specified, USD will be the default, change as needed
Replace the following code:
‘ Change these as required **************************
UnitName = “Dollar” ‘ NOTE : This is singular
SubUnitName = “Cents” ‘ NOTE : This is plural
SubUnitSingularName = “Cent” ‘ NOTE : This is singular
DecimalSeparator = “.”
‘ ***************************************************
With:
Select Case Curr
Case Is ="USD"
' Change these as required **************************
UnitName = "Dollar" ' NOTE : This is singular
SubUnitName = "Cents" ' NOTE : This is plural
SubUnitSingularName = "Cent" ' NOTE : This is singular
DecimalSeparator = "."
' ***************************************************
Case Is ="GBP"
' Change these as required **************************
UnitName = "Pound" ' NOTE : This is singular
SubUnitName = "Pence" ' NOTE : This is plural
SubUnitSingularName = "Penny" ' NOTE : This is singular
DecimalSeparator = "."
' ***************************************************
Case Is ="YEN"
' Change these as required **************************
UnitName = "Yen" ' NOTE : This is singular
SubUnitName = "Sen" ' NOTE : This is plural
SubUnitSingularName = "Sen" ' NOTE : This is singular
DecimalSeparator = "."
' ***************************************************
Case is ="RUP"
' Change these as required **************************
UnitName = "Rupee" ' NOTE : This is singular
SubUnitName = "Paisa" ' NOTE : This is plural
SubUnitSingularName = "Paisa" ' NOTE : This is singular
DecimalSeparator = "."
' ***************************************************
End Select
Now, all you have to do is to call the function passing the currency you want:
=NumToWords(A1,”GBP”)
Because the default is USD, this will work without specifying the currency, will return the number in USD: =NumToWords(A1)
Syed
Thanks a lot dear. This has really helped me a lot to create my VBA code for multi Currency to Word.
Also the suggestion by Catalin as below is great.
NumToWords(B5, B4), if the B4 cell contains the currency name, no need for nested IF’s
Catalin Bombea
Sounds great Syed, glad to hear you managed to make it work.
Cheers,
Catalin
S
Thank you so much Phil, this is much better than the tutorial on Microsoft website.
It works like a treat!
Philip Treacy
Thanks, glad it was useful to you
Yatiyana Wimalabuddhi
NumToWords() function does not work in my Windows 7. Help me.
Philip Treacy
That isn’t enough information to help you.
Please post on the forum and supply screen shots and error messages.
Regards
Phil
Musthafa
Please provide me Number to Dhirham in Excel. I hope it will inbox my mail soon.
Philip Treacy
Hi,
Have you read the section on Other Currencies and tried modifying the code to work for dhirhams?
Phil
ADAM
Hi,
I try to modified the code, but when the number with no cents, it still show “and cents”
how to make like this:
RM 1,024.00 –> Ringgit Malaysia: one thousand twenty four ringgit only
RM 1,024.25 –> Ringgit Malaysia: one thousand twenty four ringgit and twenty five cents only
RM 10.00 –> Ringgit Malaysia: Ten ringgit only
RM 0.92 –> Ringgit Malaysia: Ninety two cent only
I had adjust some in VB script
also adjust function like this =”Ringgit Malaysia: ” & NumToWords(B52) & ” Only”
thanks
L.E.:
i use excel 2013.. after add new module.. how to save the module with script? after I close excel, the module lost..
https://support.microsoft.com/en-gb/help/213360/how-to-convert-a-numeric-value-into-english-words-in-excel
I must save excel in xlsm
Select Case SubUnits
Case “”
SubUnits = “”
solved =)
Catalin Bombea
Hi Adam,
Glad to hear you found your way through.
One thing doesn’t look right:
Select Case SubUnits
Case “”
SubUnits = “”
This is practically saying that : If SubUnits=”” then SubUnits=””
So it’s a useless code, does absolutely nothing.
Cheers,
Catalin
Solange El Khoury
Hello,
Please I need to convert number into words using the Lebanese Pounds currency (LBP) with 2 decimal digits
example: 151500.50 = LBP One Hundred Fifty One Thousand Five Hundred & 50/100 only.
The numbers could get into millions but not more than that.
Could you help me with this matter.
Thank you.
Solange
Catalin Bombea
Hi Solange,
Please read Update 3 from this page – Fractional Sub Units, it’s exactly the same format you need.
Catalin
Daniel Murray
Hi Catalin,
Great routine and thanks for sharing…
I am working with the spanish version of NumToWords() and a modifed version of NumToWordsFrac() for spanish as well.
I would like the ‘Unit Name’ to appear at the end, first letter capitalized, all the rest lower case and omitting the “con” (ie. 125.00 = Ciento veinte y cinco 00/100 Dólares);
A special case is also needed for 100.00 which should read “Cien 00/100 Dólares”
Could you advise where and how to tweak your code for the above.
Many thanks and kind regards
Catalin Bombea
Hi Daniel,
You can take a look at this version, from our forum. It has an adapted code that can handle different main unit names.
If you cannot adapt the code to your situation, please sign-up to our forum and create a new topic, with your sample file and expected results.
Catalin
Vibhor Godhane
I am from India and I want ₹ 1,12,250.00 to convert as One Lakh Twelve Thousand Two Hundred Fifty Rupees and Zero Paise. I did by replacing Dollar with Rupees and Cents with Paise(Singular Cent=Paisa), but the problem is it display One Hundred Twelve Thousand Two Hundred Fifty Rupees and Zero Paise. If i write ₹ 11,12,250.00, it should display Eleven Lakh Twelve Thousand Two Hundred Fifty Rupees and Zero Paise. but it shows One Lakh One Hundred Twelve Thoudand Two Hundred Fifty Rupees and Zero Paisa. How to Get this as Indian Version ?
Catalin Bombea
Hi Vibhor,
You can upload a sample file with your code on our forum (sign-up and create a new topic), we will help you.
Cheers,
Catalin
RAYEES
HI
I WANT ONLY TO MENTION IN EXCEL AS BELOW
1000
ONE THOUSAND ONLY
Catalin Bombea
Hi Rayees,
Try this file. You will have to edit the currency, you can leave that blank “”, looks like you don’t want to use currency names.
Catalin
Francisca Garcia
Hi,
Thank you for sharing such a useful resource! I need the code in portuguese. I’ve managed to adapt your resource somewhat from the spanish version which is close enough but there are some specifics i need further assistance with if you don’t mind.
1) I need to be able to output an “and” between number levels, i.e. 1 200 as “one thousand and two hundred”; 1 520 110 as one million and five hundred and twenty thousand and one hundred and ten… you get the idea…?
2) I need to differentiate singular and plural forms of thousands, millions etc.
i.e. one million, two millions… and so on.
L.E.:
oh and in the case of millions, I need to add a “of” before the currency: e.g. ” five millions of currency” or “one billion of currency”.
thank you in advance for the help.
Regards,
FG
Catalin Bombea
Hi Francisca,
I made a few changes to the code, here it is: Num To Words – Francisca
You have to adjust again the unit and subunit names.
If you need more help on this, please upload your code on our forum (create a new topic), it will be easier to help you.
Catalin
rafael
I’ve been trying to write this but no success yet. $1005 as one thousand and five dollars and then
1000040 as one million and fourty dollars.
rafael
I placed this code in the get hundred code but i get thousand and one Thousand and five dollars for 1005 instead of one thousand and five dollars.
*************
If Mid(MyNumber,2,Len(MyNumber)-2) =”00″ and Mid(MyNumber,2,len(MyNumber)-1)”0″ Then
Result = GetDigit(Mid(MyNumber,1,1)) & “Thousand And ”
End If
Catalin Bombea
Hi Rafael,
You can try changing the GetTens and GetDigit functions:
Edit all those emunerations like:
Result = “Forty ” with Result = “and Forty ”
Or:
GetDigit = “Five” wit GetDigit = “and Five”
But I doubt that it will work as expected, it’s not designed to work like that, for example, 1040040 will look weird…
If you don’t want to display subunits when there are no decimals in the number, replace:
SubUnits = ” and No ” & SubUnitName
with:
SubUnits = “”
rafael
even if I try that, it still puts an and even infront of the whole number. The idea I wanna use is this, if there’s no number for a particular numbers place, you should put an “and” in it’s place. If there’s more than one “and” following each other just use one “and”. Thus 1040040 becomes One million and forty thousand thousand dollars and forty cents.
rafael
sorry, 1040040 becomes One million and forty thousand and forty dollars
Catalin Bombea
Hi Rafael,
Can you please create a new topic on our forum with this problem? You can upload there your sample file with the code and a few examples of how the result should be displayed. It will be a lot easier to understand each other.
Catalin
RjMagma
how to change the currency from dollar into peso (Philippine Currency)with fractions
Catalin Bombea
Hi,
At the end of the article, you will find the Update 3 – Fractional Sub Units. In the first part of the article, you will find instructions for how to convert to Other Currencies, apply them to Update 3 code.
Catalin
phalgun
Hi,
Thanks for the code. Could you help me convert it from million, billion etc to Lakh and Crore?
ie 100,000 = 1 Lakh and 10,00,000 = 10 Lakh, 1,00,00,00,00= 1 Crore
L.E.:
correction:
1,00,000 = 1 Lakh
10,00,000 = 10 Lakhs
1,00,00,000 = 1 Crore
10,00,00,000 = 10 Crores
100,00,00,000= 100 Crores
Catalin Bombea
Hi,
The code is for converting numbers to words, not for currency conversion, it will not display 10 Crore for example.
Whatever numbers you send to function, they will be converted as is, so I suggest making the conversions before ending the number to the function:
=NumToWords(450000000000/10000000) to change the base of the number. Why do you have groups of 2 and 3 zero’s in those numbers? They don’t look right.
Catalin
jIE.ORAYT
how can i do like this in my subunit?
i want to look like this
312.23 = THREE HUNDRED TWELVE & 23/100 CENTS ONLY
10.01 = TEN & 1/100 CENT ONLY
THANK YOU FOR YOUR ANSWER 😀
Catalin Bombea
Hi,
Please check the Update 3 – Fractional Sub Units, which is at the end of the article, it is exactly what you need.
Catalin
Dhiren Bhankharia
Excellent code among so many I have searched om net. But still I have problems as in India we use currency as “Thousand”, “Lacs” and “Crore”.
Catalin Bombea
Hi Dhiren,
Can you upload a sample file with correct conversions? Use our forum to create a new topic and upload the file.
Catalin
Ahmed Zaki
It is an excellent formation for which I highly appreciate the effort you have spent for making it.
What about the Kuwaiti Dinar which has three digits for fraction?
123.123 which is written as One Hundred Twenty Three Kuwait Dinars and One Hundred Twenty Three Fils
Catalin Bombea
Hi Ahmed,
This is what needs to be in code, in order to have 3 decimal places:
If DecimalPlace > 0 ThenSubUnits = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & "000", 3))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
I replaced GetTens with GetHundreds for subunits.
At the beginning of the code, there is a place where you can type your currency unit name and subunits name, as described in the article.
Catalin
rogelyn palada
need the 100 excel tips.thanks
Catalin Bombea
Hi,
Here is a link: sign-up-for-100-excel-tips-and-tricks
Cheers,
Catalin
Staback
Could the code be displayed in such a way to display cheque style writing?
For example ,
5312.12 = five thousand three hundred and twelve and cents twelve only
300.12 = three hundred and cents twelve only
312.12 = three hundred and twelve and cents twelve only
300 = three hundred only
90 = ninety only
Catalin Bombea
Hi Staback,
Have you tried this version: convert-numbers-currency-to-words-with-excel-vba#comment-36979?
Seems to be very close to what you need, with small changes, it may work as you want.
SunnyKow
I prefer not to allow my users to touch the codes to adjust the currency as most are not familiar with VBA. My suggested workaround for them is to use the SUBSTITUTE function to replace the default currency shown. In my case replace RINGGIT with USD and SEN with CENTS and so on.
Philip Treacy
Hi Sunny,
Good point. I guess the UDF could be written to take the currency and units as parameters too, thus avoiding the need to change the code to specify those.
Cheers
Phil
Ahmed Zaki
What about the currencies that have 3 decimals such as Kuwait Dinars? How can we adjust the function to work with such currencies?
Catalin Bombea
Hi Ahmed,
You have to locate this line in the code:
SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))Replace it with this line:
SubUnits = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & "000", 3))This will work for currencies with up to 3 decimals.
For translating the entire code, you have some examples above, it should be fairly easy to follow the instructions.
Let us know if you managed to make it work.
Cheers,
Catalin
ANILDAS
THANKS
Tony Chaar
HI,
Can you help giving us the way on how to round up or down the cents. Example: my cell is set to rounding 124.76 will show 125 but the code is giving (One Hundred Twenty Four US Dollars and Seventy Six Cents.
Any chance to have like: One Hundred Twenty Five US Dollars?
Thank you
Tony
Catalin Bombea
Hi Tony,
Instead of using the function like:
=NumToWords(124.76)
You can pass to the function a number already rounded:
=NumToWords(Round(124.76,0))
The round function will pass 125 to the NumToWords function, instead of 124.76, the function will convert as you wanted.
Instead of ROUND, you can use ROUNDUP, or ROUNDDOWN, CEILING, FLOOR, or any similar function that will round the number as desired.
Cheers,
Catalin
Chantal
Hi.
I thought i would help my husband with his excel sheet. He needs the difference from the time someone walks in to the time they leave. So i changed the format of his times from 08h00 and 08h45 to 08:00 and 08:45. I then added the formula eg: =G2-B2 and i got 00:45. i thought this was awesome as i was so easy. He now says he wants it to read 45 minutes. I am stumped, how do i do that?
Catalin Bombea
Hi Chantal,
Format the cell containing the formula as number, not a Time format, and change the formula to:
=(G2-B2)*24*60to convert to decimal system.Cheers,
Catalin
Gerardine
Hello, I would like to enquire if I want the end result to read as:-
For example – 33,016.68 (USD THIRTY THREE THOUSAND SIXTEEN AND CENTS SIXTY-EIGHT ONLY)
May I know how to modify / create the vba code in excel to get as above?
I’m trying to simplify my work and minimize mistakes in the office.
Any help you could provide is greatly appreciated, thank you!
Gerardine
Catalin Bombea
Hi Gerardine,
Have you tried the file at the end of the post? NumToWords.w.Fractions.xlsm
You will only have to add USD in front of the result string, which should be easy, let me hnow if you managed to make the change.
Cheers,
Catalin
Gerardine
Hi Catalin,
Thanks for your kind reply.
Just took a re-look at my excel file and found that I will need the end result to read as below:-
For example. 10,256.88 (TEN THOUSAND TWO HUNDRED FIFTY SIX AND CENTS EIGHTY-EIGHT ONLY).
– End result needs to show all the English wordings in capital form.
– ‘Dollars’ need not appear.
– ‘Cents’ need to appear before the wording.
– ‘Only’ appear at the end.
I would really appreciate if you could help me in writing the vba code for this as I am really bad at this aspect of Excel.
Many thanks!
Gerardine
Catalin Bombea
Hi Gerardine,
Please test this file from our OneDrive folder.
It should work as you described.
Cheers,
Catalin
Kevin
Catalin,
That’s perfect. All working!
Thank you very much for your help!
Kevin
Catalin Bombea
You’re wellcome Kevin 🙂
Cheers,
Catalin
Kevin
Hi there, great work!
I would like to amend this slightly though, have tried but not managed successfully. I want it to read in UK English, for example: £666,521.10 would be “Six hundred AND sixty six thousand five hundred and twenty one pounds ten pence”.
I tried adding AND after the hundreds unit but it does not work in all cases!
Any help appreciated!
Thanks
Kevin
Catalin Bombea
Hi Kevin,
You have to make the change in the Function GetHundreds(ByVal MyNumber)
Look for this Original Line:
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "Modify it to:
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred And "To remove the And displayed before decimals, as you example shows, look at the end of the main function (NumToWords), you will see these lines:
Case ""SubUnits = " and No " & SubUnitName
Case "One"
SubUnits = " and One " & SubUnitSingularName
Case Else
SubUnits = " and " & SubUnits & " " & SubUnitName
Simply remove ” and” from these strings.
Here is what i have after making those changes:
Six Hundred And Sixty Six Thousand Five Hundred And Twenty One Pounds Ten Pence
Let me know if you succeeded 🙂
Cheers,
Catalin
Kevin
Catalin,
Many thanks for your help! Nearly there! It worked really well however if you enter 100 you get:
“One Hundred and Pounds No Pence”
Any thoughts?
Thank you Kevin
Catalin Bombea
Hi Kevin,
GetHundreds function is still the key of the problem:
Replace this part in your GetHundreds function:
' Convert the hundreds place.If Mid(MyNumber, 1, 1) <> "0" And (Mid(MyNumber, 2, 1) <> "0" Or Mid(MyNumber, 3, 1) <> "0") Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred And "
Else
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
Cheers,
Catalin
Kevin
Catalin,
THat’s great thank you!
Works perfectly for one hundred. I did a little testing but it still falls over when entering 1,000! It says ‘Hundred One Thousand Pounds No Pence’
Again any help appreciated!
Thanks
Kevin
Catalin Bombea
Hi Kevin,
Try this then:
If Mid(MyNumber, 1, 1) <> "0" And (Mid(MyNumber, 2, 1) <> "0" Or Mid(MyNumber, 3, 1) <> "0") ThenResult = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred And "
ElseIf Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
Catalin
ELINOR
Hi Ms. Mynda!
Is it possible to have two currency units here, because i have to convert from Saudi Riyal to US dollars for the reason of needing to prepare Bank transfer transactions?
Thank you.
Philip Treacy
Hi Elinor,
This macro is for converting a number to words so wouldn’t do what you want. To do what you want is possible but would require us to write a custom macro. If you are interested in this please open a Helpdesk ticket and we can discuss it further.
Regards
Phil
Ani
Two words! GREAT JOB.
But how to use for 3 digit decimal currencies
Philip Treacy
Hi Ani,
Thank you. I’ll have to look at this and see what we can do for 3 digit decimals.
Phil
ottoy
how to convert words to numbers/currency
tnx
Catalin Bombea
Hi, you can find more informations on this subject on this tutorial: convert-numbers-currency-to-words-with-excel-vba
Catalin
Umair
Hi nice efforts by programmer
I want result in this form “Dollars One Hundred Fifty Eight Thousand Seven Hundred & Forty Six only” how’s it possible? Waiting for your prompt response
Thanks
Catalin Bombea
Hi Umair,
look in the NumToWords function, at the end you will see this line:
NumToWords = Application.Trim(Units & SubUnits)
You can change to:
NumToWords = “Dollars” & Application.Trim(Units & ” & ” & SubUnits) & ” Only”
Or to any combination that suits you, if you don’t want subunits to show, just remove that part.
Cheers,
Catalin
chastine
Hi!
Was wondering if you could help me out. I was looking for a function that will convert the decimals into a x/100 format. For example: 1,500.50 will become “One Thousand Five Hundred Dollars and 50/100 only”.
I tried working around the code above but I kind of got lost.
Thank you.
Mynda Treacy
Hi Chastine,
You can use a number format for that. Simply select the cells you want formatted then press CTRL+1 to open the Format Cells dialog box > then go to the Number tab > Choose Fraction from the cateogry list > choose ‘As hundredths’ from the Type list.
Kind regards,
Mynda
chastine
Hi Mynda!
Thank you so much for your reply. Can I combine that with the NumToWords Function? Sorry, am trying to help make our cheque preparation faster but I’m not too familiar with creating functions.
Thank you again!
Chastine
Philip Treacy
Hi Chastine,
no you can’t combine the two.
I’ve modified my code though so it does what you want,you can download the workbook from here.
Regards
Phil
Roshan
How to do it on Excel 2007 all work books
Philip Treacy
Hi Roshan,
Just download the 2007 workbook and then insert the code into your workbook. You then use the code like any other function.
For example if you wanted to convert a number in A1 to words, then in A2 you would enter =NumToWords(A1)
Regards
Phil
Najeeb hamayun
what about the currencies with three decimals e.g Omani Riyal?
Catalin Bombea
Hi Najeeb,
Here is a description of what needs to be changed to have 3 decimal places: https://www.myonlinetraininghub.com/convert-numbers-currency-to-words-with-excel-vba#comment-44776
Catalin
herecomesbrod
Had to write a check printing program for our office in Dom Rep – pleased with myself when I found the Microsoft kb. Wrote the code, sent the file and they pointed out that they needed it in Spanish…oh yes…err. Then I found your excellent page and used Pablo’s translation. Life saver! Thank you very much.
Philip Treacy
Fantastic! Great to hear that international collaboration is going on 🙂
Phil
edwardnguyen
I having this text, it very good. But I want to take for Japansese number. Please help me.
Take note:from 100 to 199
example: 100 = hyaku
but when i input 100 then = ichihyaku is wrong.
100 = [ hyaku ] is that right.
please help me,
thanks
Option Explicit'Main Function
Function JPY(ByVal MyNumber)
'Written by Philip Treacy
'https://www.myonlinetraininghub.com/convert-numbers-currency-to-words-with-excel-vba
'Feb 2014
'Based on code from Microsoft https://support.microsoft.com/kb/213360
Dim Units As String
Dim SubUnits As String
Dim TempStr As String
Dim DecimalPlace As Integer
Dim Count As Integer
Dim DecimalSeparator As String
Dim UnitName As String
Dim SubUnitName As String
Dim SubUnitSingularName As String
' Change these as required **************************
' UnitName = "Dollar" ' NOTE : This is singular
' SubUnitName = "Cents" ' NOTE : This is plural
' SubUnitSingularName = "Cent" ' NOTE : This is singular
' DecimalSeparator = "."
' ***************************************************
UnitName = "" ' NOTE : This is singular
SubUnitName = "円" ' NOTE : This is plural
SubUnitSingularName = "千" ' NOTE : This is singular
DecimalSeparator = "."
ReDim Place(9) As String
Place(2) = " 千 "
Place(3) = " 教区 "
Place(4) = " 十億"
Place(5) = " 百億 "
' Convert MyNumber to STRING and TRIM white space
MyNumber = Trim(CStr(MyNumber))
'If MyNumber is blank then exit
If MyNumber = "" Then
JPY = ""
Exit Function
End If
' Find Position of decimal place, 0 if none.
DecimalPlace = InStr(MyNumber, DecimalSeparator)
' Convert SubUnits and set MyNumber to Units amount.
If DecimalPlace > 0 Then
SubUnits = GetTens(left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
TempStr = GetHundreds(Right(MyNumber, 3))
If TempStr "" Then Units = TempStr & Place(Count) & Units
If Len(MyNumber) > 3 Then
MyNumber = left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Units
Case ""
Units = "" & UnitName & ""
Case ""
Units = "" & UnitName
Case Else
Units = Units & " " & UnitName & ""
End Select
Select Case SubUnits
Case ""
SubUnits = "" & SubUnitName
Case ""
SubUnits = "" & SubUnitSingularName
Case Else
SubUnits = "" & SubUnits & " " & SubUnitName
End Select
JPY = Application.Trim(Units & SubUnits)
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
' If Mid(MyNumber, 1, 1) "0" Then
'
' Result = GetDigit(Mid(MyNumber, 1, 1)) & "百"
'
' ElseIf Mid(MyNumber, 1, 1) = "100 to 199" Then
'
' Result = GetDigit(Mid(MyNumber, 1, 2)) & "百"
'
' Else
If Mid(MyNumber, 1, 3) = "100 to 199" Then
Result = GetDigit(Mid(MyNumber, 1, 0)) & "百"
ElseIf Mid(MyNumber, 1, 3) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & "百"
Else
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "十"
Case 11: Result = "十一"
Case 12: Result = "十二"
Case 13: Result = "十三"
Case 14: Result = "十四"
Case 15: Result = "十五"
Case 16: Result = "十六"
Case 17: Result = "十七"
Case 18: Result = "十八"
Case 19: Result = "十九"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(left(TensText, 1))
Case 2: Result = "二十"
Case 3: Result = "三十 "
Case 4: Result = "四十 "
Case 5: Result = "五十"
Case 6: Result = "六十 "
Case 7: Result = "七十 "
Case 8: Result = "八十 "
Case 9: Result = "九十"
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "一"
Case 2: GetDigit = "二"
Case 3: GetDigit = "三"
Case 4: GetDigit = "四"
Case 5: GetDigit = "互"
Case 6: GetDigit = "六"
Case 7: GetDigit = "七"
Case 8: GetDigit = "八"
Case 9: GetDigit = "九"
Case Else: GetDigit = ""
End Select
End Function
Catalin Bombea
Hi Edward,
Please upload a sample file to our Help Desk with your code and with a few examples of how a specific number should look when it’s properly converted to text.
We’ll do our best to help you 🙂
Catalin
edwardnguyen
thank you Catalin Bombea
Catalin Bombea
You’re wellcome 🙂
Anne Smith
Hello. Wonder if you can help. I get your newsletter delivered to my business e-mail. Whenever I try to print it (even when size reduction is selected) it will not fit on a standard printed 8×11 page (left and right columns severely cut off). Any suggestions?
My thanks, Anne Smith
Mynda Treacy
Hi Anne,
Sorry you’re having trouble printing our newsletters. What I recommend is that you view the newsletter on the web by clicking the link at the top of the email.
Copy the URL for the page, in this case it’s https://www.myonlinetraininghub.com/convert-numbers-currency-to-words-with-excel-vba
Then go to PrintFriendly.com and paste the link in the field (it’ll be obvious where to paste it) to get a print preview. PrintFriendly allows you to remove components you don’t want before you print to your printer or PDF or email.
Kind regards,
Mynda.
Grainne Duggan
Mynda
As a variation of this, how would you take a long number such as $1,256,954.84 and convert to words $1.26 Million. Also the same function for Thousands etc. Rather than have it convert the whole number, round it in the text only?
Gráinne
Mynda Treacy
Hi Grainne,
You can do that with a custom number format:
$0.00,, “Million”
No need for VBA 🙂
Kind regards,
Mynda.
Bryan Metz
Looks good. I will usually make parameters that might change but are almost always the same into optional arguments with default values. So in your case instead of having the embedded UnitName, SubUnitName, etc, I would declare the function like so:
Function NumToWords(ByVal MyNumber As Variant, Optional UnitName As String = "Dollar", Optional SubUnitName As String = "Cents", _Optional SubUnitSingularName As String = "Cent", Optional DecimalSeparator As String = ".") As String
This way if you are using the defaults you’d call it the same way, but if you wanted to change it only then would you have to add the other parameters. If you regularly use multiple currencies, you could set up multiple functions that call NumToWords with default parameters. So
PoundsToWords(MyNumber)would simply callNumToWords(MyNumber, "Pounds", "Pence", "Penny", "."), and so on.Then this also opens you up to more options: maybe code it so that if SubUnitSingularName is blank, then it will use SubUnitName for both, or if DecimalSeparator is blank, then it will use Application.International(xlDecimalSeparator) instead, and so on.
Or maybe I just usually go a little overboard coding for situations that will never arise…
Philip Treacy
Excellent Bryan. Yes these are all great suggestions and if I get time next week I’ll try to adapt the code.
I don’t think you are going overboard, I like the approach of making the code as generic as possible so it can fit as many situations as possible – and hence be more useful.
Thanks
Phil