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: