| [ Convert Number to DOLLAR (UDF NUM2DOLLAR) ] Let's Look & Find ---> You Can Convert Number to DOLLAR Sample - 1 NUM2DOLLAR (Number) Default Money Display = Dollar(s) Sample - 2 NUM2DOLLAR (Number, "fran") | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Used Formula ...(With Running MicrosoftExcel Ver 2003) | ||||
| No | Addr' | If use below Formula, You'll Get Result as Right | Result | Etc |
| 1 | B1 | =NUM2DOLLAR(A1) | One Dollar. | |
| 2 | B1 His Formula Used This Cell -> B1:B33 | |||
| 3 | B34 | =NUM2DOLLAR(A34,"franc") | One franc. | |
| 4 | B34 His Formula Used This Cell -> B34:B35 | |||
How about this suggest? | ||||
Option Explicit
Function NUM2DOLLAR(WhatsNumber As Double, Optional MoneyType As String = "Dollar")
Dim iLoop As Integer 'Loop
Dim CommaNumbr(1 To 5) As String 'Share by 3 Digit
Dim StrngNum_1 As String 'String Number to Format 000 000...
Dim StrngNum_2 As String 'String Number of Decimal
Dim WorkNumber As Variant '000 000... Split
Dim NumInt As String 'Number Integer
Dim NumDec As String 'Decimal Number
Dim PosDot As Integer
Dim PosDec As Integer
Dim DecCent As String
On Error Resume Next
CommaNumbr(1) = " Trillion ": CommaNumbr(2) = " Billion "
CommaNumbr(3) = " Million ": CommaNumbr(4) = " Thousand "
PosDot = InStr(WhatsNumber, ".")
If WhatsNumber = 0 Then
NUM2DOLLAR = "No Dollar."
If MoneyType <> "Dollar" Then
NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
End If
Exit Function
ElseIf WhatsNumber = 1 Then
NUM2DOLLAR = "One Dollar."
If MoneyType <> "Dollar" Then
NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
End If
Exit Function
End If
If PosDot = 0 Then
StrngNum_1 = Format(WhatsNumber, " 000 000 000 000 000")
Else
StrngNum_1 = Format(Left(WhatsNumber, PosDot - 1), " 000 000 000 000 000")
StrngNum_2 = Mid(WhatsNumber, PosDot + 1)
PosDec = Len(StrngNum_2)
End If
WorkNumber = Split(StrngNum_1)
For iLoop = 1 To UBound(WorkNumber)
If WorkNumber(iLoop) <> "000" Then
NumInt = NumInt & String_1(Left(WorkNumber(iLoop), 1)) & String_2(Right(WorkNumber(iLoop), 2)) & CommaNumbr(iLoop)
End If
Next
If NumInt = "" Then
NumInt = "Zero Dollar"
Else
NumInt = NumInt & " Dollars "
End If
If PosDot = 0 Then
NUM2DOLLAR = NumInt
Else
If PosDec = 1 Then
NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2 & "0") & " Cents "
ElseIf PosDec = 2 Then
NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2) & " Cents "
Else
DecCent = Left(StrngNum_2, 2)
If DecCent = "00" Then
NumDec = String_2(DecCent) & " NoCent "
Else
NumDec = String_2(DecCent) & " Cents and"
End If
For iLoop = 3 To PosDec
If Mid(StrngNum_2, iLoop, 1) = "0" Then
NumDec = NumDec & " Zero"
Else
NumDec = NumDec & " " & String_3(Mid(StrngNum_2, iLoop, 1))
End If
Next
NumDec = NumDec & " "
NUM2DOLLAR = NumInt & " and " & NumDec
End If
End If
NUM2DOLLAR = RTrim(Replace(NUM2DOLLAR, " ", " "))
If MoneyType <> "Dollar" Then
NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType & "'")
'If Want "francs" Instead of "franc's" then
'Use This
'NUM2Dollar = Replace(NUM2Dollar, "Dollar", MoneyType)
End If
NUM2DOLLAR = NUM2DOLLAR & "."
End Function
Function String_1(MyString As String) ' 100's Number
If MyString <> "0" Then
String_1 = String_3(MyString) & " Hundred "
End If
End Function
Function String_2(MyString As String) ' 10's Number (20-90)
Select Case Left(MyString, 1)
Case "1"
String_2 = String_21(MyString)
Exit Function
Case "2": String_2 = "Twenty ": Case "3": String_2 = "Thirty "
Case "4": String_2 = "Forty ": Case "5": String_2 = "Fifty "
Case "6": String_2 = "Sixty ": Case "7": String_2 = "Seventy "
Case "8": String_2 = "Eighty ": Case "9": String_2 = "Ninety "
End Select
String_2 = String_2 & String_3(Right(MyString, 1))
End Function
Function String_21(MyString As String) ' 10-19's Number
Select Case MyString
Case "10": String_21 = "Ten": Case "11": String_21 = "Eleven"
Case "12": String_21 = "Twelve": Case "13": String_21 = "Thirteen"
Case "14": String_21 = "Fourteen": Case "15": String_21 = "Fifteen"
Case "16": String_21 = "Sixteen": Case "17": String_21 = "Seventeen"
Case "18": String_21 = "Eighteen": Case "19": String_21 = "Nineteen"
End Select
End Function
Function String_3(MyString As String) ' 1-9's Number
Select Case MyString
Case "1": String_3 = "One": Case "2": String_3 = "Two"
Case "3": String_3 = "Three": Case "4": String_3 = "Four"
Case "5": String_3 = "Five": Case "6": String_3 = "Six"
Case "7": String_3 = "Seven": Case "8": String_3 = "Eight"
Case "9": String_3 = "Nine"
End Select
End Function
Any Bug Or Ask ... Mail Or Post Reply
