Sometimes we have to write Amount
both in figures and in words in Excel but there is no such inbuilt function in
Excel that can help you to convert the figures into words in Indian way. You
have to convert the figures manually and then type the words into excel cells. It’s
both error prone and time taking. This
post teaches you how to create a user-defined function that will solve the
problem.

- Make your Excel workbook macro enabled .
- Open the ‘Microsoft Visual Basic’ by pressing ‘Alt+F11’.
- Go to menu ‘Insert > Module’. An editing window will open.
- Simply copy the code (blue coloured text) given below and paste on the ‘Microsoft Visual Basic’ code editor.
- Save the Module Code in ‘Microsoft Visual Basic’ and close it.
- Use the user defined function ‘RupeesPaise’ in Excel.

Option Explicit

'This function can convert upto
11 digit & 2 decimal digit into words

'as Rupees and Paise in
Indian method . e.g. 99,99,99,99,999.99 will be converted as

'Rupees Ninety Nine Arab
Ninety Nine Crore Ninety Nine Lakh Ninety Nine Thousand

'Nine Hundred Ninety Nine
Paise Ninety Nine

'Main Function

Function RupeesPaise(ByVal
MyNumber)

Dim Rupees, Paise

Dim DecimalPlace

Dim Tens, Hundreds, Thousands, Lakhs,
Crores, Arabs

' String representation of
amount.

MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0
if none.

DecimalPlace = InStr(MyNumber,
".")

' Convert Paise and set
MyNumber to Rupees amount.

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber,
DecimalPlace + 1) & _

"00", 2))

MyNumber = Trim(Left(MyNumber,
DecimalPlace - 1))

End If

'Sets the length of mynumber
11 digits if it's less

If Len(MyNumber) = 0 Then

MyNumber = "00000000000"
& MyNumber

ElseIf Len(MyNumber) = 1 Then

MyNumber = "0000000000" &
MyNumber

ElseIf Len(MyNumber) = 2 Then

MyNumber = "000000000" &
MyNumber

ElseIf Len(MyNumber) = 3 Then

MyNumber = "00000000" &
MyNumber

ElseIf Len(MyNumber) = 4 Then

MyNumber = "0000000" &
MyNumber

ElseIf Len(MyNumber) = 5 Then

MyNumber = "000000" &
MyNumber

ElseIf Len(MyNumber) = 6 Then

MyNumber = "00000" &
MyNumber

ElseIf Len(MyNumber) = 7 Then

MyNumber = "0000" &
MyNumber

ElseIf Len(MyNumber) = 8 Then

MyNumber = "000" &
MyNumber

ElseIf Len(MyNumber) = 9 Then

MyNumber = "00" &
MyNumber

ElseIf Len(MyNumber) = 10 Then

MyNumber = "0" & MyNumber

Else

MyNumber = MyNumber

End If

' Create string that express
Thousand

Tens = GetTens(Left(Right(MyNumber, 2), 2))

' Create string that express
Hundred

If Left(Right(MyNumber, 3), 1) =
"0" Then

Hundreds = ""

Else

Hundreds =
GetDigit(Left(Right(MyNumber, 3), 1)) & " Hundred "

End If

' Create string that espress
Thousand

If Left(Right(MyNumber, 5), 2) =
"00" Then

Thousands = ""

Else

Thousands =
GetTens(Left(Right(MyNumber, 5), 2)) & " Thousand "

End If

'Create string that express
Lakh

If Left(Right(MyNumber, 7), 2) =
"00" Then

Lakhs = ""

Else

Lakhs = GetTens(Left(Right(MyNumber,
7), 2)) & " Lakh "

End If

' Create string that espress
Crore

If Left(Right(MyNumber, 9), 2) =
"00" Then

Crores = ""

Else

Crores = GetTens(Left(Right(MyNumber,
9), 2)) & " Crore "

End If

' Create string that express
Arab

If Left(Right(MyNumber, 11), 2) =
"00" Then

Arabs = ""

Else

Arabs = GetTens(Left(Right(MyNumber,
11), 2)) & " Arab "

End If

Rupees = Arabs & Crores
& Lakhs & Thousands & Hundreds & Tens

' Insert the word
"Rupees"

If Rupees = "" Then

Rupees = ""

ElseIf Rupees = "One" Then

Rupees = "Rupee" & Rupees

Else

Rupees = "Rupees " &
Rupees

End If

' Insert the word
"Paise"

If Paise = "" Then

Paise = ""

Else

Paise = " Paise " & Paise

End If

RupeesPaise = Rupees & Paise

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

If you face any problem write comment below.

## 4 comments:

For example :- 810,801

We say

1) Eight Hundred and Ten

2) Eight Hundred and One

But it shows

1) Eight Hundred Ten

2) Eight Hundred One

Problems:

We say 101,110

One Hundred and One,

One Hundred and Ten.

But these steps says

One Hundred One,

One Hundred Ten.

The word "and" not in any value. so please check and repost it. it should be valuable one. Thanks

Didnot worked...

Please give continuous script instead of putting instructions in green.

Please upload only blue coloured script.

Thaks.

@udayk. It works sound. The green lines do not create any problem so long the sign ' are at the begining of each green line. While running the function if you face any error message write that message in the comment box. I'll check it.

Post a Comment