I have written a VB Macro in Excel.
There is a function ‘Convert()’. It Takes one argument, that is the cell reference of the number written in figure.
So, you just take a new excel file. Click ‘Tools’ Menu, ‘Macro’, ‘Visual Basic Editor’.
In ‘Visual Basic Editor’, Click ‘Insert’ Menu, ‘Module’.
In that module copy & paste following code.
Then for example in cell ‘A1′, write ‘5000.59’, then in cell ‘B1′, write ‘Convert(A1)’ and you will get solution to your problem.
*************************************CODE*************************************
Public Function Convert(Figure As String) As String
‘to pass the figure as argument from worksheet for conversion
‘and return the text string
Dim rWrd As String
Dim rFgr As Currency
Dim pWrd As String
Dim pFgr As Currency
On Error GoTo erHand1
If Figure = “.” Then
Figure = “0.”
ElseIf Not (LTrim(Figure) = “” Or Figure = “0.” Or Val(Figure) = “0” Or Figure = “0.0”) Then
‘to find out paise part
pFgr = Paise(Figure)
pWrd = psWord(pFgr) ‘and convert into word
‘to find out rupees part
rFgr = Rupees(Figure)
rWrd = rsWord(rFgr) ‘and convert into word
‘to make a continuous string and show it
Convert = MakeStr(rWrd, pWrd)
End If
Exit Function
erHand1:
If Err = 13 And Not Figure = “” Then
MsgBox “Please enter valid number.”
End If
If Err = 6 Then
MsgBox “Number is too big.”
End If
If Err = 5 Then
‘MsgBox Err
End If
End Function
‘————————————————————————————————————————————–
Public Function Paise(X As String) As Currency
‘to separate paise part
Paise = Val(Right(CStr(Round(CCur(X), 2) * 100), 2))
End Function
‘————————————————————————————————————————————–
Public Function Rupees(X As String) As Currency
‘to separate rupees part
If Len(CStr(Round(CCur(X), 2) * 100)) > 1 Then
Rupees = Val(Left(CStr(Round(CCur(X), 2) * 100), Len(CStr(Round(CCur(X), 2) * 100)) – 2))
Else
Rupees = 0
End If
End Function
‘————————————————————————————————————————————–
Public Function psWord(X As Currency) As String
‘to convert paise in word
If Len(CStr(X)) = 2 Then
psWord = TwoDigit(CInt(X))
Else
psWord = OneDigit(CInt(X))
End If
End Function
‘————————————————————————————————————————————–
Public Function rsWord(X As Currency) As String
‘to convert rupees in word
Dim Cro As String
Dim Lac As String
Dim Tho As String
Dim Hun As String
Dim Ten As String
If Len(CStr(X)) = 9 Then
Cro = TwoDigit(Mid(CStr(X), 1, 2))
Lac = TwoDigit(Mid(CStr(X), 3, 2))
Tho = TwoDigit(Mid(CStr(X), 5, 2))
Hun = OneDigit(Mid(CStr(X), 7, 1))
Ten = TwoDigit(Mid(CStr(X), 8, 2))
ElseIf Len(CStr(X)) = 8 Then
Cro = OneDigit(Mid(CStr(X), 1, 1))
Lac = TwoDigit(Mid(CStr(X), 2, 2))
Tho = TwoDigit(Mid(CStr(X), 4, 2))
Hun = OneDigit(Mid(CStr(X), 6, 1))
Ten = TwoDigit(Mid(CStr(X), 7, 2))
ElseIf Len(CStr(X)) = 7 Then
Lac = TwoDigit(Mid(CStr(X), 1, 2))
Tho = TwoDigit(Mid(CStr(X), 3, 2))
Hun = OneDigit(Mid(CStr(X), 5, 1))
Ten = TwoDigit(Mid(CStr(X), 6, 2))
ElseIf Len(CStr(X)) = 6 Then
Lac = OneDigit(Mid(CStr(X), 1, 1))
Tho = TwoDigit(Mid(CStr(X), 2, 2))
Hun = OneDigit(Mid(CStr(X), 4, 1))
Ten = TwoDigit(Mid(CStr(X), 5, 2))
ElseIf Len(CStr(X)) = 5 Then
Tho = TwoDigit(Mid(CStr(X), 1, 2))
Hun = OneDigit(Mid(CStr(X), 3, 1))
Ten = TwoDigit(Mid(CStr(X), 4, 2))
ElseIf Len(CStr(X)) = 4 Then
Tho = OneDigit(Mid(CStr(X), 1, 1))
Hun = OneDigit(Mid(CStr(X), 2, 1))
Ten = TwoDigit(Mid(CStr(X), 3, 2))
ElseIf Len(CStr(X)) = 3 Then
Hun = OneDigit(Mid(CStr(X), 1, 1))
Ten = TwoDigit(Mid(CStr(X), 2, 2))
ElseIf Len(CStr(X)) = 2 Then
Ten = TwoDigit(Mid(CStr(X), 1, 2))
ElseIf Len(CStr(X)) = 1 Then
Ten = OneDigit(Mid(CStr(X), 1, 1))
End If
If Not Cro = “” Then
rsWord = Cro & ” Crore ”
End If
If Not Lac = “” Then
rsWord = rsWord & Lac & ” Lakh ”
End If
If Not Tho = “” Then
rsWord = rsWord & Tho & ” Thousand ”
End If
If Not Hun = “” Then
rsWord = rsWord & Hun & ” Hundred ”
End If
If Not Ten = “” Then
rsWord = rsWord & Ten
End If
End Function
‘————————————————————————————————————————————–
Private Function OneDigit(X As Integer) As String
‘to convert one digit no. ( 1 to 9) in word
If X = 1 Then
OneDigit = “One”
ElseIf X = 2 Then
OneDigit = “Two”
ElseIf X = 3 Then
OneDigit = “Three”
ElseIf X = 4 Then
OneDigit = “Four”
ElseIf X = 5 Then
OneDigit = “Five”
ElseIf X = 6 Then
OneDigit = “Six”
ElseIf X = 7 Then
OneDigit = “Seven”
ElseIf X = 8 Then
OneDigit = “Eight”
ElseIf X = 9 Then
OneDigit = “Nine”
Else
OneDigit = “”
End If
End Function
‘————————————————————————————————————————————–
Private Function TwoDigit(X As String) As String
‘to convert two digit no. (10 to 19 & multiples of 10) in word
If X = “10” Then
TwoDigit = “Ten”
ElseIf X = “11” Then
TwoDigit = “Eleven”
ElseIf X = “12” Then
TwoDigit = “Twelve”
ElseIf X = “13” Then
TwoDigit = “Thirteen”
ElseIf X = “14” Then
TwoDigit = “Fourteen”
ElseIf X = “15” Then
TwoDigit = “Fifteen”
ElseIf X = “16” Then
TwoDigit = “Sixteen”
ElseIf X = “17” Then
TwoDigit = “Seventeen”
ElseIf X = “18” Then
TwoDigit = “Eighteen”
ElseIf X = “19” Then
TwoDigit = “Nineteen”
ElseIf X = “20” Then
TwoDigit = “Twenty”
ElseIf X = “30” Then
TwoDigit = “Thirty”
ElseIf X = “40” Then
TwoDigit = “Fourty”
ElseIf X = “50” Then
TwoDigit = “Fifty”
ElseIf X = “60” Then
TwoDigit = “Sixty”
ElseIf X = “70” Then
TwoDigit = “Seventy”
ElseIf X = “80” Then
TwoDigit = “Eighty”
ElseIf X = “90” Then
TwoDigit = “Ninety”
Else
TwoDigit = OtherTwoDigit(X)
End If
End Function
‘————————————————————————————————————————————–
Private Function OtherTwoDigit(X As String) As String
‘to convert other two digit no. in word
If Left(CStr(X), 1) = “2” Then
OtherTwoDigit = “Twenty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “3” Then
OtherTwoDigit = “Thirty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “4” Then
OtherTwoDigit = “Fourty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “5” Then
OtherTwoDigit = “Fifty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “6” Then
OtherTwoDigit = “Sixty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “7” Then
OtherTwoDigit = “Seventy ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “8” Then
OtherTwoDigit = “Eighty ” & OneDigit(Right(CStr(X), 1))
ElseIf Left(CStr(X), 1) = “9” Then
OtherTwoDigit = “Ninety ” & OneDigit(Right(CStr(X), 1))
Else
OtherTwoDigit = OneDigit(Right(CStr(X), 1))
End If
End Function
‘————————————————————————————————————————————–
Private Function MakeStr(X As String, Y As String) As String
‘to make final output (join rupees and paise and make a string)
If X = “” And Y = “” Then
MsgBox “This no. is very big. Please enter no. upto 9 digits.”
MakeStr = “”
ElseIf X = “” Then
MakeStr = “Paise ” & Y & ” only.”
ElseIf Y = “” Then
MakeStr = “Rs. ” & X & ” only.”
Else
MakeStr = “Rs. ” & X & ” and Paise ” & Y & ” only.”
End If
End Function
Discuss This Question: