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

N Drup

Jun 29, 2021 - 18:03

How to go upto 100 Crs? this code has limitations upto 10 cr.

Karthikeyan

May 04, 2020 - 06:22

In reply to by HARSH Mehta

Hi, please check the latest version

stella

Apr 26, 2020 - 21:34

In reply to by kiros

Admin please reply I have same question

stella

Apr 26, 2020 - 21:22

In reply to by REHMAN

Please say how to add and in middle as above.

stella

Apr 26, 2020 - 20:45

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.

Karthikeyan

Mar 22, 2020 - 19:29

In reply to by Rajesh

Give me more information, so I can check it.

Karthikeyan

Mar 22, 2020 - 19:28

In reply to by Rajesh

Yes, this script will work on all excel versions. But not tested in older versions below 2003.

kiros

Mar 16, 2020 - 12:39

For this "and", what modification should be done?
eg. 669501 = Six Lakh Sixty Nine Thousand Five Hundred And One Only

sarvesh Maheshwari

Dec 22, 2019 - 09:04

What will be this function's google sheet version please update with the same.

Rajesh

Nov 22, 2019 - 10:37

In reply to by Karthikeyan

because im trying this its not working im getting error..

Rajesh

Nov 22, 2019 - 10:35

In reply to by Karthikeyan

Hai Karthik is this permanent script for all excell.

admin

Nov 20, 2019 - 08:00

In reply to by cyborg

Let me know sample values to check

cyborg

Nov 17, 2019 - 19:22

Some times I get a difference of one paise less. How to remove that error?

Parul Sahu

Aug 20, 2019 - 16:46

sir, please give the code which could work in visual basic 2010

aCe_vEnTurA2

Feb 28, 2019 - 14:22

In reply to by admin

Thanks a million. That helped a lot. You are a star :)

Nories Wilson

Dec 11, 2018 - 09:57

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

Chetan

Jul 27, 2018 - 19:04

In reply to by Karthikeyan

where to save this file, and I am not able to see the save as option as "default template" Pl help

varinder singh

Jul 13, 2018 - 08:25

Thanks team, This code is wonderful and very usefull for me.

Balu

May 26, 2018 - 17:05

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?

harichand

May 25, 2018 - 11:58

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

Karthikeyan

Apr 23, 2018 - 19:46

In reply to by Ritesh

You have to save the file as default template to activate it by default.

Ritesh

Apr 20, 2018 - 19:42

In reply to by Karthikeyan

i tried saving it .xlsm but it works in the same file not on all new excel files

Abhinav Binkar

Apr 04, 2018 - 13:07

In reply to by Karthikeyan

Can you help me with this.
If the value is 0.00 then it should come as "Rupees NIL only".

Sourav Lubana

Mar 29, 2018 - 00:38

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.

Karthikeyan

Mar 28, 2018 - 07:35

In reply to by Gaurav Gupta

Yes, change the number to round figure using formula, before using this convert formula.

Gaurav Gupta

Mar 28, 2018 - 01:33

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

admin

Mar 08, 2018 - 12:35

In reply to by aCe_vEnTurA2

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

aCe_vEnTurA2

Mar 08, 2018 - 11:07

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 ?

Shufa S

Feb 23, 2018 - 17:26

In reply to by admin

Where do I send this? Please give me your email address

admin

Feb 23, 2018 - 17:18

In reply to by Shufa S

Pls send me the file with sample data.

Shufa S

Feb 23, 2018 - 17:05

I am getting this error #NAME?
I have saved the file in .xlsm as suggested.

admin

Feb 16, 2018 - 12:56

In reply to by gudapati nages…

1) 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.

gudapati nages…

Feb 16, 2018 - 11:55

In reply to by admin

PREVIOUS SAVED FILES ALSO NOT OPEN SIR

admin

Feb 16, 2018 - 11:30

In reply to by mohammed faisal

Save the file as Macro enabled workbook (.xlsm)

admin

Feb 16, 2018 - 11:29

In reply to by gudapati nages…

Did you save the file with .xlsm format? Save the file as Macro enabled workbook (.xlsm).

Kamal Bharakhda

Feb 16, 2018 - 04:24

Admin, does this code works in Early Binding and Late Binding?

gudapati nages…

Feb 09, 2018 - 10:13

In reply to by gudapati nages…

SIR,
AWAITING FOR YOUR REPLY SIR

mohammed faisal

Dec 31, 2017 - 22:41

if i close the xcel file the module which i have created vanishes...need Assistance..

saravanan

Dec 28, 2017 - 16:21

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

Bikas

Dec 09, 2017 - 16:39

In reply to by Kalyan Babu C

Can you help me?

Rohit Kulshreshtha

Dec 06, 2017 - 00:26

Wow... this is amazing..thank you very much

Aditya Naman Singh

Nov 16, 2017 - 00:10

sir if i want of one trillion ??? please help

Deepak

Nov 13, 2017 - 21:03

Worked very well. Thank you.

Karthikeyan

Nov 13, 2017 - 20:18

In reply to by Imran Habib

Please save the file with .xlsm extension (Macro-enabled workbook)