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

Karthikeyan

Nov 13, 2017 - 20:18

In reply to by Dhanashree Sawant

Please explain the issue in brief

Dhanashree Sawant

Nov 13, 2017 - 12:16

Im not able to apply this on my excel file..

Imran Habib

Nov 12, 2017 - 13:17

Dear Bro
I have problem in formula when i done what your description and it is work properly than i save document and when i open again than it is not work it showing (#Name). i hope you understand my problem any solution of this error please inform me i want fix it permanently thank

admin

Nov 06, 2017 - 14:40

In reply to by Arwinder Singh

Replace the lines 75 to 83 with the below:

' Clean up rupees.
Select Case Rupees
Case ""
Rupees = ""
Case "One"
Rupees = "One"
Case Else
Rupees = Rupees
End Select

Arwinder Singh

Nov 06, 2017 - 12:08

Dear Sir, I do not need "Rupees" word in this module.
Kindly help ,, Arwinder ,9781787817

Karthikeyan

Oct 18, 2017 - 13:11

In reply to by gudapati nages…

Save the file as Macro enabled workbook. .xlsm

gudapati nages…

Oct 18, 2017 - 12:58

In reply to by Karthikeyan

THANK YOU SIR, IT'S WORKING
AND
AGAIN SMALL ISSUE SIR, ONCE THE FILE IS CLOSED AND RE-OPENED, THEN THE TYPE OF THE COMMAND WILL COME TO BE FOLLWED BY WHAT HOW TO DO IT, PLEASE PROVIDE SOLUTION. https://uploads.disquscdn.c...

Karthikeyan

Oct 16, 2017 - 20:45

In reply to by gudapati nages…

Replace the lines 75 to 83 with the below:

' Clean up rupees.
Select Case Rupees
Case ""
Rupees = ""
Case "One"
Rupees = "One"
Case Else
Rupees = Rupees
End Select

gudapati nages…

Oct 16, 2017 - 12:33

https://uploads.disquscdn.c... SIR,
I HAVE SMALL CORRECT IN THIS CODE, I DONT WANT RUPEES WORD FRONT OF THE AMOUNT PLEASE TELL ME HOW TO EDIT THAT LINE URGENT

Karthikeyan

Oct 06, 2017 - 08:23

In reply to by Manjunath S

Use the latest code given above.

Karthikeyan

Oct 06, 2017 - 08:20

In reply to by Naresh Rana

Code need to be edited. If you still need it, reply me.

Karthikeyan

Oct 06, 2017 - 08:19

In reply to by Sathish Kumar

Yes, use Excel formula to round the value.
=ROUND()

Karthikeyan

Sep 11, 2017 - 18:01

In reply to by KAUSHIK GUHA

Yes, you can easily do this. use ROUND funtion in excel.
Example : = *ConvertCurrencyToEnglish(ROUND(A1));*

KAUSHIK GUHA

Sep 11, 2017 - 17:56

Sir
Is there any option in your formula that can be modified?
It is noticed that, your downloaded idea couldn't support with my satisfaction, i.e. say one invoice value is Rs. 11018.70 and i want this amount convert in words as Rs 11019, how can i do this, can you please help me in this regard.

Thanking you
Kaushik Guha

Karthikeyan

Sep 08, 2017 - 18:10

In reply to by Ameen

Decimal places also will work fine. Can you give me an example?

Ameen

Aug 31, 2017 - 21:46

In reply to by Karthikeyan

Thanks for your quick reply.

However the current code is giving syntax error. Would appreciate greatly if you can give solution. It worked earlier. Not it is showing syntax error when source cell value is above 9 or if it has decimal.

Ameen

Aug 30, 2017 - 00:08

Is there anyway to install this module permanently in my system? I don't want to save the file as macro enabled everytime to execute this.

Roshan

Aug 07, 2017 - 12:17

Dear sir, i just applied spellnumber formula in excel sheet but that is applicable for single sheet when i make is save as or open the new sheet that is not exist where i need to follow the same procedure as i did for the first one pls suggest thank u.

Kumar

Jul 27, 2017 - 13:38

It's working for the file saved as xlsm, but not for any other files.

Sathish Kumar

Jul 26, 2017 - 19:33

https://uploads.disquscdn.c...

Hi,
Can you please suggest me, for round value of the amount that convert in words...

For Example
---> "48,541.64" is taken as "48,542" in words

sayyad shaherali

Jul 26, 2017 - 16:10

Many Thanks to share code.
Great job.

Karthikeyan

Jul 22, 2017 - 17:06

In reply to by Tanvir

Use formula in Excel to round the value.

vishal

Jul 22, 2017 - 16:56

Thank you very much i was fed up trying so many codes and add ins everytime there was a error library not found etc etc. This worked very well in excel 2010

chethan PC

Jul 17, 2017 - 12:39

how to set admin excel sheet 2007

chethan PC

Jul 17, 2017 - 12:23

how to use Admin work sheet 2007 excel

Tanvir

Jul 16, 2017 - 01:12

Is is possible to print only rupees even if we have value after decimal points.
for eg value is 100.20 but we only want it to print "Rupee one hundred only". What ever value is there after decimal should be simply ignored

Karthikeyan

Jul 15, 2017 - 16:33

In reply to by Tanuj @ The Re…

The cell number can be provided as like =ConvertCurrencyToEnglish(F28)

Karthikeyan

Jul 15, 2017 - 16:32

In reply to by Priya R

Create new template in Excel and add this code and save the template. Next time, when you open new Excel workbook, select that template.

Priya R

Jul 15, 2017 - 16:09

is it possible to make this function as default in all excel sheets?

Tanuj @ The Re…

Jul 03, 2017 - 12:58

How do I make it show the number from a specific cell? I mean, I want the words to be fetched from, for example cell F28, how do I do that? Right now it seems that the number needs to be entered in the "=ConvertCurrencyToEnglish()" function. Thanks.

Naresh Rana

May 30, 2017 - 16:17

how i wrote rs. only in last of line for example four thousand nine hundred rs. only

Karthikeyan

May 27, 2017 - 14:57

In reply to by Rishabh

Save the file as default template.

Rishabh

May 27, 2017 - 14:27

How can we put this formula default in all Excel File in our PC

Karthikeyan

Mar 31, 2017 - 19:28

In reply to by laltu mondal

​Did you enable macros?
Post the screenshot. ​

laltu mondal

Mar 31, 2017 - 19:18

I Cannot use this function after reopen the sheet

RAGHAV

Mar 27, 2017 - 16:34

THANK YOU SO MUCH...

admin

Mar 02, 2017 - 19:17

In reply to by Abhinav Binkar

Hi, code is updated as per your wish. Re-download / update the code from the above links.

admin

Mar 02, 2017 - 19:16

In reply to by REHMAN

Hi, code updated, download / update the code from the above links.

admin

Mar 02, 2017 - 19:15

In reply to by akm2020

Hi, code updated, download or update from the above links.

admin

Mar 02, 2017 - 19:09

In reply to by HARSH Mehta

Hi, Re-download or update the code from be above links. your request updated.

admin

Mar 02, 2017 - 19:08

In reply to by kanakarajuboddi

Hi, Files updated, Re-download or update the code from be above links

Manjunath S

Feb 27, 2017 - 13:31

how to add word Rupees in start like Rupees One thousand

Karthikeyan

Feb 26, 2017 - 18:44

In reply to by Forid

Provide a screenshot with the formula visible.

Karthikeyan

Feb 26, 2017 - 18:43

In reply to by Vaidali Pawar

Try saving the document with .xlsm extension.
Otherwise, you have some restriction enabled in your system.

Forid

Feb 08, 2017 - 15:02

Dear Karthikeyan,
I tried to use this module, but i have faced a problem. It shows #NAME?. (Invalid Name error) and opened again the VBA tools. What is the exact problem.
Thanks you for the code.

Janardhan Bantwal

Feb 01, 2017 - 18:06

thanks a lot.... it helped me..
Could you please help me spell 3 decimal places...?

Ex. if i type 750.650
it will be ***One Thousand Rials and 650/1000 Only***
or

***One Thousand Rials and Six Hundred fifty Baiza Only***

looking forward for your help

Karthikeyan

Jan 12, 2017 - 17:38

In reply to by kanakarajuboddi

Just use the formula like this
=ConvertCurrencyToEnglish() &" only"

admin

Jan 12, 2017 - 17:36

In reply to by HARSH Mehta

Just use the formula like this
'="Rupees " &ConvertCurrencyToEnglish() &" only"'