Tuesday, August 17, 2010
Convert a numeric value to English words
Convert a numeric value to English words:
I have found many location where people who use excel they want to change the numeric value to words. Following code can help you for the same !!!
Option Explicit
Public Numbers As Variant, Tens As Variant
Sub updateArrayNums()
Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub
Function WordToNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
If Abs(MyNumber) > 999999999 Then
WordToNum = "Value too large"
Exit Function
End If
updateArrayNums
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
For n = 3 To 1 Step -1
StrNo = Format(ValNo(n), "000")
If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
If Temp1 <> "" Then Temp2 = Temp2 & " and "
Else
Temp2 = ""
End If
If n = 3 Then
If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
WordToNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordToNum = Trim(Temp2 & Temp1 & " thousand " & WordToNum)
If n = 1 Then WordToNum = Trim(Temp2 & Temp1 & " million " & WordToNum)
End If
Next n
NumStr = Trim(Str(Abs(MyNumber)))
' check after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = " point"
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
Next n
WordToNum = WordToNum & Temp1
End If
If Len(WordToNum) = 0 Or Left(WordToNum, 2) = " p" Then
WordToNum = "Zero" & WordToNum
End If
End Function
Function GetTens(TensNum As Integer) As String
' Converts a num to txt from 0 to 99.
If TensNum <= 19 Then
GetTens = Numbers(TensNum)
Else
Dim MyNo As String
MyNo = Format(TensNum, "00")
GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function
enjoy VBA coding...
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.