Convert Numbers (currency) to text

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
I am using the attached code for converting numbers (currency) into text. The code was working fine but now gives a
"Compile error : Cant find project or library"

please help to find what has gone wrong now. the code stops at "ReDim Place(9) As String" line.



VBA Code:
Function SpellIndian(ByVal MyNumber)
Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lac "
    Place(4) = " Crore "
    Place(5) = " Arab " ' String representation of amount
    MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
    DecimalPlace = InStr(MyNumber, ".")
     ' Convert Paise and set MyNumber to Rupee amount
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
        If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
        If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
        If Count = 1 And Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            If Count > 1 And Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        Count = Count + 1
     Loop
    Select Case Rupees
    Case ""
        Rupees = "No Rupees"
    Case "One"
        Rupees = "One Rupee"
    Case Else
         '****************************************************************
         'modified the following two lines to display "Rupees" to precede
         ' rem'd the first line and added the second line
         '****************************************************************
         'Rupees = Rupees & " Rupees"
        Rupees = "Rupees " & Rupees
       
    End Select
    Select Case Paise
    Case ""
         '****************************************************************
         'modified the following two lines to display nothing for no paise
         ' rem'd the first line and added the second line
         '****************************************************************
       
         'Paise = " and No Paise"
         '****************************************************************
         'modified the following line to display " Only" for no paise
         ' rem'd the first line and added the second line
         '****************************************************************
         'Paise = "" Paise = "Only"
    Case "One"
        Paise = " and One Paisa"
    Case Else
        Paise = " and " & Paise & " Paise"
       
    End Select
    SpellIndian = Rupees & Paise
   
End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
     'Convert the tens and ones place
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
    Dim Result As String
    Result = "" ' null out the temporary function value

    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
        Select Case Val(TensText)
        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 ' If value between 20-99
        Select Case Val(Left(TensText, 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
        Result = Result & GetDigit _
        (Right(TensText, 1)) 'Retrieve ones place
    End If
    GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
    End Select
End Function
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Works for me!
Maybe some corruption in Excel. Try repair/reinstall Office.
 
Upvote 0
Copy this formula into the cell where you want the text to appear.
Change all of the the E5 cell addresses to the cell address where the number is located.


=IF(AND(E5<1,E5<>0),"Zero Dollars and "&RIGHT(TEXT(E5,"000000000.00"),2)&" Cents",IF(E5=0,"",CHOOSE(LEFT(TEXT(E5,"000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--LEFT(TEXT(E5,"000000000.00"))=0,,IF(AND(--MID(TEXT(E5,"000000000.00"),2,1)=0,--MID(TEXT(E5,"000000000.00"),3,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(E5,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(E5,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(E5,"000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),
CHOOSE(MID(TEXT(E5,"000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--LEFT(TEXT(E5,"000000000.00"))+MID(TEXT(E5,"000000000.00"),2,1)+MID(TEXT(E5,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(E5,"000000000.00"),4,1)+MID(TEXT(E5,"000000000.00"),5,1)+MID(TEXT(E5,"000000000.00"),6,1)+MID(TEXT(E5,"000000000.00"),7,1))=0,(--MID(TEXT(E5,"000000000.00"),8,1)+RIGHT(TEXT(E5,"000000000.00")))>0),"Million ","Million "))
&CHOOSE(MID(TEXT(E5,"000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(E5,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(E5,"000000000.00"),5,1)=0,--MID(TEXT(E5,"000000000.00"),6,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(E5,"000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(E5,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(E5,"000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(E5,"000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--MID(TEXT(E5,"000000000.00"),4,1)+MID(TEXT(E5,"000000000.00"),5,1)+MID(TEXT(E5,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(E5,"000000000.00"),7,1)+MID(TEXT(E5,"000000000.00"),8,1)+MID(TEXT(E5,"000000000.00"),9,1))=0,--MID(TEXT(E5,"000000000.00"),7,1)<>0),"Thousand ","Thousand "))
&CHOOSE(MID(TEXT(E5,"000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(E5,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(E5,"000000000.00"),8,1)=0,--MID(TEXT(E5,"000000000.00"),9,1)=0),"Hundred ","Hundred "))&
CHOOSE(MID(TEXT(E5,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(E5,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(E5,"000000000.00"),9,1)+1,"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&"Dollars ",CHOOSE(MID(TEXT(E5,"000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))

&"and "&RIGHT(TEXT(E5,"000000000.00"),2)&" Cents"))
 
Upvote 0
Very impressive Mega formula John.
However, doesn't work for OP which is in Indian currency units.
Ones
Tens
Hundreds
Thousands
Lakhs (100 thousand)
Crores (10 million)
99,99,99,999.99 = Rupees ninety nine crore, ninety nine lakh, ninety nine thousand, nine hundred and ninety nine and Paise ninety nine.
 
Upvote 0
Thank you for the compliment, but I cannot take ALL thre credit.
I found most of this formula online, but I had to modify it to show:

0.25 = Zero and 25 cents.

I'm sorry, but I'm not familiar with the format of OP in Indian currency units.
Perhaps, if you can give me some different examples, I can see what I can do.
 
Upvote 0
Here are some numbers using the OPs code:
1.12​
One Rupee and Twelve Paise
7.84​
Rupees Seven and Eighty Four Paise
54.88​
Rupees Fifty Four and Eighty Eight Paise
384.16​
Rupees Three Hundred Eighty Four and Sixteen Paise
2,689.12​
Rupees Two Thousand Six Hundred Eighty Nine and Twelve Paise
18,823.84​
Rupees Eighteen Thousand Eight Hundred Twenty Three and Eighty Four Paise
1,31,766.88​
Rupees One Lac Thirty One Thousand Seven Hundred Sixty Six and Eighty Eight Paise
9,22,368.16​
Rupees Nine Lac Twenty Two Thousand Three Hundred Sixty Eight and Sixteen Paise
64,56,577.12​
Rupees Sixty Four Lac Fifty Six Thousand Five Hundred Seventy Seven and Twelve Paise
4,51,96,039.84​
Rupees Four Crore Fifty One Lac Ninety Six Thousand Thirty Nine and Eighty Four Paise
31,63,72,278.88​
Rupees Thirty One Crore Sixty Three Lac Seventy Two Thousand Two Hundred Seventy Eight and Eighty Eight Paise
 
Upvote 0
This is not a formula that you put into a cell, it's a VBA macro,
I found these two YouTube videos that may help you.


 
Upvote 0
Drsarao,

I modified this macro function to solve your issue.
With the exception of your first exampole (1.12) I think it is exactly what you're looking for.

Create a Module and copy/paste this into it.
Then type =SpellNumber(cell address) where you want the text.

ex. =SpellNumber(C5)

Good Luck !

VBA Code:
Function SpellNumber(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One "
WORDs(2) = "Two "
WORDs(3) = "Three "
WORDs(4) = "Four "
WORDs(5) = "Five "
WORDs(6) = "Six "
WORDs(7) = "Seven "
WORDs(8) = "Eight "
WORDs(9) = "Nine "
WORDs(10) = "Ten "
WORDs(11) = "Eleven "
WORDs(12) = "Twelve "
WORDs(13) = "Thirteen "
WORDs(14) = "Fourteen "
WORDs(15) = "Fifteen "
WORDs(16) = "Sixteen "
WORDs(17) = "Seventeen "
WORDs(18) = "Eighteen "
WORDs(19) = "Nineteen "
tens(2) = "Twenty "
tens(3) = "Thirty "
tens(4) = "Fourty "
tens(5) = "Fifty "
tens(6) = "Sixty "
tens(7) = "Seventy "
tens(8) = "Eighty "
tens(9) = "Ninety "
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If
If Val(Left(FIGURE, 9)) > 1 Then
SpellNumber = "Rupees "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
SpellNumber = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & "Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & "Lac "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & "Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + "Hundred "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & "and "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & "Paise "
End If
End Function
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top