Convert Currency in Number to Words (Indian Rupees) - MS Excel

Submitted by Karthikeyan on

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


Download this

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

Inserting VBA module in MS Office

  • 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()'

Currency in Number to Worlds - Excel

 

 

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

admin

Jan 12, 2017 - 17:35

In reply to by Hemal mehta

Save the file as 'Excel Macro Enabled Workbook (*.xlsm)'

kanakarajuboddi

Jan 12, 2017 - 12:15

In reply to by admin

how to change code convert in words put "only" in cash denomination

Karthikeyan

Jan 04, 2017 - 12:54

In reply to by Hemal mehta

Save the excel file *.xlsm extension. It will work.

Hemal mehta

Jan 04, 2017 - 00:33

It is not getting saved
Everytym I open excel again i have to reenter d code

Hemal mehta

Jan 03, 2017 - 23:56

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

Vipin

Sep 30, 2016 - 20:24

Thanks a lot for this. But IBM Symphony shows error while executing this code whereas in Excel it works fine. Please suggest.

Karthikeyan

Sep 24, 2016 - 07:51

In reply to by Dhruba

What error did you see in print preview?

Dhruba

Sep 23, 2016 - 21:57

In reply to by Karthikeyan

Sir, 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 ?

Dhruba

Sep 05, 2016 - 13:14

In reply to by Karthikeyan

If the value is null how it works ?

Dhruba

Sep 05, 2016 - 12:25

In reply to by Karthikeyan

It works sir, Finally I got new problem with null value of Amount Field(Numeric Field). The snap shots are as below :

Dhruba

Sep 05, 2016 - 12:07

In reply to by Karthikeyan

Thank you Sir, You save my life.

Karthikeyan

Sep 05, 2016 - 10:53

In reply to by Dhruba

No need to change regional settings. Do you security warning tab with 'Enable Content' button. If so, click and enable it.

Dhruba

Sep 05, 2016 - 10:39

In reply to by Karthikeyan

Still 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

Karthikeyan

Sep 05, 2016 - 07:44

In reply to by Dhruba

Make sure u unblocked the file for execution. Right click on the file and go to properties, check on Unblock.

Dhruba

Sep 04, 2016 - 15:22

In reply to by Karthikeyan

This my error

Dhruba

Sep 04, 2016 - 15:21

In reply to by Karthikeyan

Even I could not run your file downloaded. same error occurs . I am running Ms access 2013

Karthikeyan

Sep 04, 2016 - 11:14

In reply to by Dhruba

Attach screenshot pls.

Dhruba

Sep 04, 2016 - 11:07

In reply to by Karthikeyan

I received #Name? error

Karthikeyan

Sep 03, 2016 - 22:55

In reply to by Dhruba

What error did you get?​

Dhruba

Sep 03, 2016 - 22:52

In reply to by Karthikeyan

Sir, I cant execute it on MS access 2013

Abhinav Binkar

Aug 25, 2016 - 13:56

In reply to by Karthikeyan

Wowwwwww. Superrrrrr. Awesome.
Thanks a lot Karthikeyan sir.
I'm so happy.

Abhinav Binkar

Aug 25, 2016 - 11:27

In reply to by Karthikeyan

Thanks 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.

Karthikeyan

Aug 25, 2016 - 09:10

In reply to by Abhinav Binkar

The above code is already updated.

Karthikeyan

Aug 25, 2016 - 09:07

In reply to by Kishore Mandlik

If paise is zero it will not show Zero paise only. It will only end with .. rupees only

Kishore Mandlik

Aug 19, 2016 - 18:42

thanks this is working however the Zero word is not coming.. can you please help

Srinath G

Aug 01, 2016 - 11:26

Thank you sir, works wonderfully well...

Abhinav Binkar

Jul 24, 2016 - 12:24

In reply to by Jacob Kuruvilla

Hmm. Ok.

Jacob Kuruvilla

Jul 24, 2016 - 12:00

In reply to by Abhinav Binkar

there is no usage like Two crores and two lakhs.... Two crore and two lakh is the correct usage..

Abhinav Binkar

Jun 18, 2016 - 01:05

In reply to by Karthikeyan

I 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]

Karthikeyan

Jun 17, 2016 - 15:33

In reply to by Abhinav Binkar

The 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.

Abhinav Binkar

Jun 17, 2016 - 12:22

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.

Shashikala Menon

Jun 17, 2016 - 11:31

Thank you so much, it has helped me a lot

Karthikeyan

Jun 03, 2016 - 16:33

In reply to by Chandra Shaker

Just use a space before 'O'.
= ConvertCurrencyToEnglish(A1) &"* o*nly"

Chandra Shaker

Jun 03, 2016 - 15:54

In reply to by Karthikeyan

Hi 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.

Sagar Gogate

Jun 02, 2016 - 15:47

In reply to by Karthikeyan

Thank 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

Karthikeyan

Jun 02, 2016 - 14:16

In reply to by Sagar Gogate

If 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.

Sagar Gogate

Jun 01, 2016 - 16:52

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

Karthikeyan

Apr 26, 2016 - 12:23

In reply to by REHMAN

The question unrelated to the blog post published here. Pls ask your question in our forum. Also, send a sample excel sheet.

REHMAN

Apr 26, 2016 - 11:58

dear page break is not working still
not show in preview out side of break area

Karthikeyan

Apr 26, 2016 - 08:09

In reply to by REHMAN

If 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

REHMAN

Apr 25, 2016 - 09:53

dear how to remove page break in excel 2007

Karthikeyan

Apr 23, 2016 - 22:06

In reply to by REHMAN

I can't understand clearly.

Karthikeyan

Apr 23, 2016 - 22:05

In reply to by REHMAN

It needs more modification in code. Currently it adds 'and' only if paise present.

REHMAN

Apr 23, 2016 - 15:31

dear plz give me extra hlp
how to remove page break
sheet working normal
show all new working in preview

REHMAN

Apr 23, 2016 - 14:52

thnx dear how to add word AND in middle like (four hundred AND fifty)