Cheques, invoices, bills, receipts are needs to have the currency format both in Numerical and words. The below script can be used in Microsoft Excel / Word / Access or any VBA program supported applications to convert the currency in number format to words (English).
The below script adds 'and' joining word if it has paise (decimal part), otherwise if you want to have 'and' before 10s or 100s please check the following link for the version 2 of this script
Convert Currency in Number to Words (Indian Rupees) - Version 2
Function ConvertCurrencyToEnglish(ByVal MyNumber)
' Edited by Karthikeyan [email protected]
Dim Temp
Dim Rupees, Paise
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " lakh "
Place(4) = " Crore "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paise
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
' Hi! Note the above line Mid function it gives right portion
' after the decimal point
'if only . and no numbers such as 789. accures, mid returns nothing
' to avoid error we added 00
' Left function gives only left portion of the string with specified places here 2
Paise = ConvertTens(Temp)
' Strip off paise from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
If MyNumber <> "" Then
' Convert last 3 digits of MyNumber to Indian Rupees.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
End If
' convert last two digits to of mynumber
Count = 2
Do While MyNumber <> ""
Temp = ConvertTens(Right("0" & MyNumber, 2))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 2 Then
' Remove last 2 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
' Clean up rupees.
Select Case Rupees
Case ""
Rupees = ""
Case "One"
Rupees = "Rupee One"
Case Else
Rupees = "Rupees " & Rupees
End Select
' Clean up paise.
Select Case Paise
Case ""
Paise = ""
Case "One"
Paise = "One Paise"
Case Else
Paise = Paise & " Paise"
End Select
If Rupees = "" Then
ConvertCurrencyToEnglish = Paise & " Only"
ElseIf Paise = "" Then
ConvertCurrencyToEnglish = Rupees & " Only"
Else
ConvertCurrencyToEnglish = Rupees & " and " & Paise & " Only"
End If
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 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
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
How to use this code?
- Click on Developer Tab in MS office (Word / Excel / Access). If Developer Tab not appears, click here to know the way to enable this.
- Click Visual Basic, then VBA application development window will open
- Now right click in Project pane, insert -> Module
- Now paste the above code and save it as Macro Enabled document. (For excel save with extension .xlsm)
- Now you can use the formula '=ConvertCurrencyToEnglish()'
MS Access & MS Word Implementations
Online converter
Update 02-03-2017 : Code updated to show 'Rupee/Rupees' as prefix and 'Only' as suffix.
How to use video :
Category
Comments
How to go upto 100 Crs? this code has limitations upto 10 cr.
In reply to by kiros (not verified)
PermalinkHi, Please check the new version
https://www.livetolearn.in/...
In reply to by stella (not verified)
PermalinkPlease check this version
https://www.livetolearn.in/...
In reply to by stella (not verified)
PermalinkHi, please check this version
https://www.livetolearn.in/...
In reply to by HARSH Mehta (not verified)
PermalinkHi, please check the latest version
In reply to by kiros (not verified)
PermalinkAdmin please reply I have same question
In reply to by REHMAN (not verified)
PermalinkPlease say how to add and in middle as above.
What if we want number to be converted in below format.please assist.am trying to modify it but couldn't do.
1750-Rupees One thousand seven hundred and fifty only.
176548-Rupees one lakh seventy six thousand five hundred and forty eight only
79056-Rupees seventy nine thousand and fifty six only.
Need to use it daily but couldn't moidfy the code.please assist.
In reply to by Rajesh (not verified)
PermalinkGive me more information, so I can check it.
In reply to by Rajesh (not verified)
PermalinkYes, this script will work on all excel versions. But not tested in older versions below 2003.
For this "and", what modification should be done?
eg. 669501 = Six Lakh Sixty Nine Thousand Five Hundred And One Only
What will be this function's google sheet version please update with the same.
In reply to by Karthikeyan
Permalinkbecause im trying this its not working im getting error..
In reply to by Karthikeyan
PermalinkHai Karthik is this permanent script for all excell.
In reply to by cyborg (not verified)
PermalinkLet me know sample values to check
Some times I get a difference of one paise less. How to remove that error?
sir, please give the code which could work in visual basic 2010
In reply to by admin
PermalinkThanks a million. That helped a lot. You are a star :)
Greetings!
Thank you very much brooo i was searching this last three days i got lots of option, but it is pure and good as i need...
Nories Wilson
In reply to by Karthikeyan
Permalinkwhere to save this file, and I am not able to see the save as option as "default template" Pl help
Thanks team, This code is wonderful and very usefull for me.
Dear friends, the above code is perfectly working. But I'm facing pronlem with the new files. If i open new Excel work book and try to change the number into words, the function is not displaying. What should I do so that this code works for all the work books?
In reply to by harichand (not verified)
PermalinkPls send email to [email protected], I will send the code.
This is the good job , thanks for this , but is have one problem in this if i don't want rupees in front for that what i should do.
please help me
In reply to by Ritesh (not verified)
PermalinkYou have to save the file as default template to activate it by default.
In reply to by Karthikeyan
Permalinki tried saving it .xlsm but it works in the same file not on all new excel files
In reply to by Karthikeyan
PermalinkCan you help me with this.
If the value is 0.00 then it should come as "Rupees NIL only".
Hi,, Can you help me in reaching result for the following figures:-
1) Rs 1,00,00,00,000 result in words as Rupees One Hundred Crore Only
2) Rs.1,00,00,00,001 result in words as Rupees One Hundred Crore One Only
3) Rs. 10,00,00,00,000 result in words as Rupees Ten Hundred Crore Only
4) Rs. 10,00,00,00,001 result in words as Rupees Ten Hundred Crore One Only
5) Rs. 19,22,45,67,891 result in words as Rupees Nineteen Hundred TwentyTwo Crore
FortyFive Lakh SixtySeven Thousand Eight Hundred Ninety One Only.
In reply to by Gaurav Gupta (not verified)
PermalinkYes, change the number to round figure using formula, before using this convert formula.
It worked and saved lot of time.
Only issue i want to get resolved is : I do not want digits to be converted after decimal place. What do i have to change in code??? I have changed my digits in round figure. So i do not want decimal places to be shown in words. Kindly help
In reply to by aCe_vEnTurA2 (not verified)
PermalinkIf you want it as default in all new workbooks, change the Excels default template, for this purpose first you have to save the macro enabled workbook as Excel template. Then, set Excel to start with that template by default. https://support.office.com/...
I am sorry I am new to Excel. I am not able to go ahead of "and save it as Macro Enabled document. (For excel save with extension .xlsm)" Do I have to do this for each and every worksheet I want it in or can I just add it to excel so that it works for whichever sheet I open. Also I do not see any option of "save as" in the module. So how can I save it as .xlsm ?
In reply to by Shufa S (not verified)
Permalink[email protected]
In reply to by admin
PermalinkWhere do I send this? Please give me your email address
In reply to by Shufa S (not verified)
PermalinkPls send me the file with sample data.
I am getting this error #NAME?
I have saved the file in .xlsm as suggested.
In reply to by gudapati nages… (not verified)
Permalink1) Try saving the file in local instead of network.
2) Save as Macrol enabled workbook (.xlsm)
3) Create a new excel file and try the above 2 steps after adding the conversion code.
In reply to by admin
PermalinkPREVIOUS SAVED FILES ALSO NOT OPEN SIR
In reply to by mohammed faisal (not verified)
PermalinkSave the file as Macro enabled workbook (.xlsm)
In reply to by gudapati nages… (not verified)
PermalinkDid you save the file with .xlsm format? Save the file as Macro enabled workbook (.xlsm).
Admin, does this code works in Early Binding and Late Binding?
In reply to by gudapati nages… (not verified)
PermalinkSIR,
AWAITING FOR YOUR REPLY SIR
https://uploads.disquscdn.c... GOOD MORNING SIR,
MY PROBLEM IS WHEN I OPEN EXCEL SHEET IT SHOWN ERROR POP PLEASE SOLVE THIS PROBLEM.
if i close the xcel file the module which i have created vanishes...need Assistance..
Dear sir, the above converter is perfectly working, but while i close and open the file i got this error message
#NAME
Please help me
In reply to by Kalyan Babu C (not verified)
PermalinkCan you help me?
Wow... this is amazing..thank you very much
sir if i want of one trillion ??? please help
Worked very well. Thank you.
In reply to by Imran Habib (not verified)
PermalinkPlease save the file with .xlsm extension (Macro-enabled workbook)