number into text or in words

N

Narnimar

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
 
N

Narnimar

Joel,
It returns like this $25000.00. But I want result in words "Dollar Twenty
Five Thousand and no Zero cent"
 
J

Joel

Here is a functtion that will do whatt you want. There is no simple way of
doing this.

Function GetCurrency(MyCurrency As Double) As String


Dim Ones(10) As String
Dim Tens(10) As String
Dim Teens(10) As String

Ones(0) = ""
Ones(1) = "One"
Ones(2) = "Two"
Ones(3) = "Three"
Ones(4) = "Four"
Ones(5) = "Five"
Ones(6) = "Six"
Ones(7) = "Seven"
Ones(8) = "Eight"
Ones(9) = "Nine"

Tens(0) = ""
Tens(1) = "Ten"
Tens(2) = "Twenty"
Tens(3) = "Thirty"
Tens(4) = "Fourty"
Tens(5) = "Fifty"
Tens(6) = "Sixty"
Tens(7) = "Seventy"
Tens(8) = "Eighty"
Tens(9) = "Ninety"

Teens(0) = "Ten"
Teens(1) = "Eleven"
Teens(2) = "Twelve"
Teens(3) = "Thirteen"
Teens(4) = "Fourteen"
Teens(5) = "Fiveteen"
Teens(6) = "Sixteen"
Teens(7) = "Seventeen"
Teens(8) = "Eighteen"
Teens(9) = "Nineteen"



StringNumber = CStr(MyCurrency)

If (InStr(StringNumber, ".") > 0) Then
MyDollars = Left(StringNumber, InStr(StringNumber, ".") - 1)
MyCents = Mid(StringNumber, InStr(StringNumber, ".") + 1)
Else
MyDollars = StringNumber
MyCents = "00"
End If


GetCurrency = ""

DollarLength = Len(MyDollars)
If ((DollarLength Mod 3) = 1) Then
MyDollars = "00" & MyDollars
DollarLength = Len(MyDollars)
Else

If ((DollarLength Mod 3) = 2) Then
MyDollars = "0" & MyDollars
DollarLength = Len(MyDollars)
End If

End If


For i = DollarLength To 1 Step -3

Nibble = Mid(MyDollars, DollarLength - i + 1, 3)


If (StrComp(Left(Nibble, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Left(Nibble, 1))) & " Hundred"
End If

If (StrComp(Mid(Nibble, 2, 1), "0") <> 0) Then

If (CInt(Mid(Nibble, 2, 2)) > 10) And _
(CInt(Mid(Nibble, 2, 2)) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Mid(Nibble, 3, 1)))

Else

If (StrComp(Mid(Nibble, 2, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Mid(Nibble, 2, 1)))
End If

If (StrComp(Mid(Nibble, 3, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))

End If
End If

Else

If (StrComp(Mid(Nibble, 3, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))
End If
End If


Select Case i

Case 6
GetCurrency = GetCurrency & " Thousands"
Case 9
GetCurrency = GetCurrency & " Millions"
Case 12
GetCurrency = GetCurrency & " Billions"
Case 15
GetCurrency = GetCurrency & " Trillions"

End Select


Next i

GetCurrency = GetCurrency & " Dollars"

If (StrComp(MyCents, "00") = 0) Then
GetCurrency = GetCurrency & " and Zero"
Else
GetCurrency = GetCurrency & " and"
End If


If (StrComp(Left(MyCents, 1), "0") <> 0) Then

If (CInt(MyCents) > 10) And _
(CInt(MyCents) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Right(MyCents, 1)))

Else

If (StrComp(Left(MyCents, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Left(MyCents, 1)))
End If

If (StrComp(Right(MyCents, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))

End If
End If

Else

If (StrComp(Right(MyCents, 1), "0") <> 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))
End If
End If

GetCurrency = GetCurrency & " Cents"

End Function
 
Top