ExcelBanter - convert number to intext

I believe I've adapted the code from Ozgrid.com so that it will work for you.

See the Ozgrid page for instructions on how to insert it into your workbook
and how to call it from a worksheet.

Here is the modified code:
Function SpellNumber(ByVal MyNumber)
'adapted from original code by
' http://www.ozgrid.com/VBA/ValueToWords.htm
'by
'J.Latham
'for use with Indian currency
'Altered/New code left justified

Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
Dim hundredsFlag As Boolean
Dim digitCount As Integer

'we can't handle values greater
'than 10^10 (beyond Arawb)
If MyNumber 10 ^ 11 Then
SpellNumber = "Too Large to Translate"
Exit Function
End If

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lakh " ' original Million
Place(4) = " Crore " ' original Billion
Place(5) = " Arawb " ' original Trillion

' String representation of amount.
MyNumber = Trim(Str(MyNumber))
'for this we will add ".00" if no
'decimal in the value
If InStr(MyNumber, ".") = 0 Then
MyNumber = MyNumber & ".00"
End If
'and pad left side to get a length of 12 characters
If Len(MyNumber) < 12 Then
MyNumber = String(12 - Len(MyNumber), "0") & MyNumber
End If

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace 0 Then
Cents = GetTens(Left(Mid(MyNumber, _
DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
hundredsFlag = False
Do While MyNumber < ""
If hundredsFlag Then
digitCount = 2
Else
digitCount = 3
End If
hundredsFlag = True ' get 2 digits after 1st pass
Temp = GetHundreds(Right(MyNumber, digitCount))
If Temp < "" Then Dollars = Temp & Place(Count) _
& Dollars
If Len(MyNumber) digitCount Then
MyNumber = Left(MyNumber, Len(MyNumber) _
- digitCount)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Dollars
Case ""
Dollars = "No Ruppees"
Case "One"
Dollars = "One Ruppee"
Case Else
Dollars = Dollars & " Ruppees"
End Select

Select Case Cents
Case ""
Cents = " and No Paisa"
Case "One"
Cents = " and One Paisa"
Case Else
Cents = " and " & Cents & " Paisa"
End Select
SpellNumber = Dollars & Cents
End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
'from original code by
' http://www.ozgrid.com/VBA/ValueToWords.htm
'UNALTERED
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)
'from original code by
' http://www.ozgrid.com/VBA/ValueToWords.htm
'UNALTERED
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
'do nothing
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
'do nothing
End Select
' Retrieve ones place.
Result = Result & GetDigit _
(Right(TensText, 1))
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
'from original code by
' http://www.ozgrid.com/VBA/ValueToWords.htm
'UNALTERED
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

"ramesh k. goyal - abohar" wrote:

I am looking for a function to convert a amount (number) to text in indian
ruppees. For example, 23456789.00 would display as "Two crore thirty four
lacs fifty six thousand seven hundred eighty nine ruppees & 00 paisa".

Pleeeeeeze send any solutions.

Thanx in advance.

Ramesh goyal