Need to modify function "spell"

[minirider]

Hi all,
the "spell" function works great, the only problem is , it doesn't fully resolve my problem. For the document i need to send to bank. I have few problems that i need to modify the code: As below
e.g.
1. Usd 45612.00 . I need it to covert for me to " us dollars: Fourty five thousand six hundred twelve only (not "0 cent")
but spell function will convert to " fourty five thousand six hundred twelve dollars and 0 cent" i need "0 cent" to be "only", i need to remove "dollar" out from text, because i already have " us dollars" up front.
2. Usd 45312.13. I need it to covert to " fourty five thousand six hundred twelve and 13/100"

many thx for every replied in adance.
Kevin

 

[footoo]

Presumably the "spell" function is a UDF.
Post the code.

 

[footoo]

Try this :

Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
    Cents = Format(Round(MyNumber - Int(MyNumber), 2) * 100, "0")
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        MyNumber = ""
    End If
    Count = Count + 1
Loop
Select Case Dollars
    Case ""
        Dollars = "No Dollars"
    Case "One"
        Dollars = "One Dollar"
     Case Else
        Dollars = Dollars & " Dollars"
End Select
Select Case Cents
    Case ""
        Cents = " Only"
    Case "One"
        Cents = " and 1/100"
    Case Else
        Cents = " and " & Cents & "/100"
End Select
SpellNumber = Dollars & Cents
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

 

[minirider]

try this :

function spellnumber(byval mynumber)
dim dollars, cents, temp
dim decimalplace, count
redim place(9) as string
place(2) = " thousand "
place(3) = " million "
place(4) = " billion "
place(5) = " trillion "
' string representation of amount.
Mynumber = trim(str(mynumber))
' position of decimal place 0 if none.
Decimalplace = instr(mynumber, ".")
' convert cents and set mynumber to dollar amount.
If decimalplace > 0 then
    cents = format(round(mynumber - int(mynumber), 2) * 100, "0")
    mynumber = trim(left(mynumber, decimalplace - 1))
end if
count = 1
do while mynumber <> ""
    temp = gethundreds(right(mynumber, 3))
    if temp <> "" then dollars = temp & place(count) & dollars
    if len(mynumber) > 3 then
        mynumber = left(mynumber, len(mynumber) - 3)
    else
        mynumber = ""
    end if
    count = count + 1
loop
select case dollars
    case ""
        dollars = "no dollars"
    case "one"
        dollars = "one dollar"
     case else
        dollars = dollars & " dollars"
end select
select case cents
    case ""
        cents = " only"
    case "one"
        cents = " and 1/100"
    case else
        cents = " and " & cents & "/100"
end select
spellnumber = dollars & cents
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

thank you so much for your assist. It's so great, you have my problems 99% solved. But will it be possible to take the word "dollars" out.
E.g.
The code you provided me, if i typed 12345.00 it will convert to " twelve thousand three hundred fourty five "dollars" only
but what i really need is "twelve thousand three hundred fourty five only" becuase i already have "us dollar:" up front

if i typed 1234.50 it will convert to " one thousand two hundred thirty four dollar and 50/100"
but what i really need is " one thousand two hundred thirty four and 50/100"

thank you so much for your kind assists.

 

[footoo]

Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
    Cents = Format(Round(MyNumber - Int(MyNumber), 2) * 100, "0")
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        MyNumber = ""
    End If
    Count = Count + 1
Loop
Select Case Dollars
    Case ""
        Dollars = "No Dollars"
    Case "One"
        Dollars = "One"
     Case Else
        Dollars = Dollars
End Select
Select Case Cents
    Case ""
        Cents = " Only"
    Case "One"
        Cents = " and 1/100"
    Case Else
        Cents = " and " & Cents & "/100"
End Select
SpellNumber = Dollars & Cents
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