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
In reply to by Hemal mehta (not verified)
PermalinkSave the file as 'Excel Macro Enabled Workbook (*.xlsm)'
In reply to by admin
Permalinkhow to change code convert in words put "only" in cash denomination
In reply to by Hemal mehta (not verified)
PermalinkSave the excel file *.xlsm extension. It will work.
It is not getting saved
Everytym I open excel again i have to reenter d code
I used to above formula but once I close the excel file it does not get saved
After reopening it again I again have to use d code n go through the entire process
Thanks a lot for this. But IBM Symphony shows error while executing this code whereas in Excel it works fine. Please suggest.
In reply to by Karthikeyan
Permalinkhttps://uploads.disquscdn.c...
In reply to by Dhruba (not verified)
PermalinkWhat error did you see in print preview?
In reply to by Karthikeyan
PermalinkSir, Now I got a problem on print preview if field is empty otherwise ok. While I click on debug it goes to code MyNumber = Trim(Str(MyNumber)) highlighed. Any solution ?
In reply to by Karthikeyan
PermalinkIf the value is null how it works ?
In reply to by Karthikeyan
PermalinkIt works sir, Finally I got new problem with null value of Amount Field(Numeric Field). The snap shots are as below :
In reply to by Karthikeyan
PermalinkThank you Sir, You save my life.
In reply to by Dhruba (not verified)
PermalinkNo need to change regional settings. Do you security warning tab with 'Enable Content' button. If so, click and enable it.
In reply to by Karthikeyan
PermalinkStill same the problem, Still in my computer regional setting is English. Should I change it to Hindi(India). Is there any code that I can only convert to word not in Rupees. For example 2030.50 = Two Thousand Thirty dot Fifty
In reply to by Dhruba (not verified)
PermalinkMake sure u unblocked the file for execution. Right click on the file and go to properties, check on Unblock.
In reply to by Karthikeyan
PermalinkThis my error
In reply to by Karthikeyan
PermalinkEven I could not run your file downloaded. same error occurs . I am running Ms access 2013
In reply to by Dhruba (not verified)
PermalinkAttach screenshot pls.
In reply to by Karthikeyan
PermalinkI received #Name? error
In reply to by Dhruba (not verified)
PermalinkWhat error did you get?
In reply to by Karthikeyan
PermalinkSir, I cant execute it on MS access 2013
In reply to by Karthikeyan
PermalinkWowwwwww. Superrrrrr. Awesome.
Thanks a lot Karthikeyan sir.
I'm so happy.
In reply to by Abhinav Binkar (not verified)
PermalinkCheck out the code in the below link :
http://goo.gl/OwsXMo
In reply to by Karthikeyan
PermalinkThanks Karthikeyan.
But can you please help to add " Only" at the end of that function. And not manually entering &" Only" in the formula.
Plz plz help sir.
In reply to by Abhinav Binkar (not verified)
PermalinkThe above code is already updated.
In reply to by Kishore Mandlik (not verified)
PermalinkIf paise is zero it will not show Zero paise only. It will only end with .. rupees only
thanks this is working however the Zero word is not coming.. can you please help
Thank you sir, works wonderfully well...
In reply to by Jacob Kuruvilla (not verified)
PermalinkHmm. Ok.
In reply to by Abhinav Binkar (not verified)
Permalinkthere is no usage like Two crores and two lakhs.... Two crore and two lakh is the correct usage..
In reply to by Karthikeyan
PermalinkI will be really thankful to you if you please create code which puts " Only" at the end of that function. Also if paise is .00 then it should Not show "and Zero Paise Only" but it should finish with Rupees...Only.
Please help Kathikeyan sir.
- Abhinav
[email protected]
In reply to by Abhinav Binkar (not verified)
PermalinkThe code is created for multi purpose use. So, I didn't include the word 'only'.
I will think about the add-on creation, I will let you know once when ready to use.
How to add "ONLY" at the end, without manually enter &" only" in the formula. Can't we use that in the function code itself?
Also how to create excel add-in file for that. Plz help Sir.
Thank you so much, it has helped me a lot
In reply to by Chandra Shaker (not verified)
PermalinkJust use a space before 'O'.
= ConvertCurrencyToEnglish(A1) &"* o*nly"
In reply to by Karthikeyan
PermalinkHi Karthikeyan,
Thank you so much for this.. As per ur suggestion i do same but in &"only" they continue the words.
LIke if we give 80 rs it show like this (Eighty Rupeesonly) actually it's look like (Eighty Rupess Only) the space is not showing. can you plz help me out for this.
appreciate your help.
In reply to by Sagar Gogate (not verified)
PermalinkSend to [email protected]
In reply to by Karthikeyan
PermalinkThank you so much for your reply. But I still face the problem.
Will you please tell me your email id or the way to send you the file.
Many thanks in advance.
Sagar
In reply to by Sagar Gogate (not verified)
PermalinkIf the file is saved as xlsm it the module also saved and it should work. If u still face the problem send me the file.
I have my file (ABC) open in which I want the numbers in words.
Then I have copy pasted the above code in module
Now when I go to File>>save ABC.xls.
I am unable to save it as xlsm.
If I click on save ABC.xls then a dialogue box appears saying 'following feature of the work book not supported by earlier versions.
I tried to save the original excel file as ABC.xlsm
and then when I go to file>>saveABC.xlsm appears.
But still 'ConvertCurrencyToEnglish' formula gives the out put as '#Name'.
I think my module is not getting saved. What it is saving is the module in the original file. Do I need to save the module differntly? If yes will you please help me with the pictures.
Thanks,
Sagar
In reply to by REHMAN (not verified)
PermalinkThe question unrelated to the blog post published here. Pls ask your question in our forum. Also, send a sample excel sheet.
dear page break is not working still
not show in preview out side of break area
In reply to by REHMAN (not verified)
PermalinkIf you want to remove all page breaks, go to Page Layout (tab) -> Breaks -> Reset All Page Breaks
If you want to remove one particular page break, place the cursor in the cell below the page break, go to Page Layout (tab) -> Breaks -> Remove Page Break
dear how to remove page break in excel 2007
In reply to by samir (not verified)
PermalinkDownload from the below link
https://goo.gl/j6u3Z4
In reply to by REHMAN (not verified)
PermalinkI can't understand clearly.
In reply to by REHMAN (not verified)
PermalinkIt needs more modification in code. Currently it adds 'and' only if paise present.
In reply to by Miraj Khan (not verified)
PermalinkCheck this post
https://www.livetolearn.in/...
dear plz give me extra hlp
how to remove page break
sheet working normal
show all new working in preview
thnx dear how to add word AND in middle like (four hundred AND fifty)