EXCEL FORMULA

1.  

1. Function Pcskill(ByVal MyNumber As Double) As String
2.     Dim NumStr As String
3.     Dim DecimalPlace As Integer, Count As Integer
4.     Dim Temp As String, Tens As String
5. 
   
6. 
   
7.     ReDim Place(9) As String
8.     Place(2) = " Thousand "
9.     Place(3) = " Million "
10.     Place(4) = " Billion "
11.     Place(5) = " Trillion "
12.     
13.     ' Convert MyNumber to string, trimming extra spaces.
14.     NumStr = Trim(Str(MyNumber))
15. 
    
16. 
    
17.     ' Find decimal place.
18.     DecimalPlace = InStr(NumStr, ".")
19. 
    
20. 
    
21.     ' Convert cents and set MyNumber to integer amount.
22.     If DecimalPlace > 0 Then
23.         Temp = GetTens(Left(Mid(NumStr, DecimalPlace + 1) & "00", 2))
24. Pcskill = Temp & " Cents"
25.         ' Strip off cents from remainder to convert.
26.         NumStr = Trim(Left(NumStr, DecimalPlace - 1))
27.     End If
28. 
    
29. 
    
30.     Count = 1
31.     Do While NumStr <> ""
32.         Temp = GetHundreds(Right(NumStr, 3))
33.         If Temp <> "" Then Pcskill = Temp & Place(Count) & Pcskill
34.         If Len(NumStr) > 3 Then
35.             NumStr = Left(NumStr, Len(NumStr) - 3)
36.         Else
37.             NumStr = ""
38.         End If
39.         Count = Count + 1
40.     Loop
41.     Pcskill = Trim(Pcskill)
42. End Function
43. 
    
44. 
    
45. ' Converts a number from 100-999 into text
46. Function GetHundreds(ByVal MyNumber As String) As String
47. Dim Result As String
48.     If Val(MyNumber) = 0 Then Exit Function
49.     MyNumber = Right("000" & MyNumber, 3) ' Ensure three digits
50.     ' Convert the hundreds place.
51.     If Mid(MyNumber, 1, 1) <> "0" Then
52.         Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
53.     End If
54.     ' Convert the tens and ones place.
55.     If Mid(MyNumber, 2, 1) <> "0" Then
56.         Result = Result & GetTens(Mid(MyNumber, 2))
57.     Else
58.         Result = Result & GetDigit(Mid(MyNumber, 3))
59.     End If
60.     GetHundreds = Result
61. End Function
62. 
    
63. 
    
64. ' Converts a number from 10 to 99 into text.
65. Function GetTens(TensText As String) As String
66. Dim Result As String
67.     Result = ""           ' Null out the temporary function value.
68.     If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
69.         Select Case Val(TensText)
70.             Case 10: Result = "Ten"
71.             Case 11: Result = "Eleven"
72.             Case 12: Result = "Twelve"
73.             Case 13: Result = "Thirteen"
74.             Case 14: Result = "Fourteen"
75.             Case 15: Result = "Fifteen"
76.             Case 16: Result = "Sixteen"
77.             Case 17: Result = "Seventeen"
78.             Case 18: Result = "Eighteen"
79.             Case 19: Result = "Nineteen"
80.             Case Else
81.         End Select
82.     Else                                 ' If value between 20-99...
83.         Select Case Val(Left(TensText, 1))
84.             Case 2: Result = "Twenty "
85.             Case 3: Result = "Thirty "
86. Case 4: Result = "Forty "
87.             Case 5: Result = "Fifty "
88.             Case 6: Result = "Sixty "
89.             Case 7: Result = "Seventy "
90.             Case 8: Result = "Eighty "
91.             Case 9: Result = "Ninety "
92.             Case Else
93.         End Select
94.         Result = Result & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
95.     End If
96.     GetTens = Result
97. End Function
98. 
    
99. 
    
100. ' Converts a number from 1 to 9 into text.
101. Function GetDigit(Digit As String) As String
102.     Select Case Val(Digit)
103.         Case 1: GetDigit = "One"
104.         Case 2: GetDigit = "Two"
105.         Case 3: GetDigit = "Three"
106.         Case 4: GetDigit = "Four"
107.         Case 5: GetDigit = "Five"
108.         Case 6: GetDigit = "Six"
109. Case 7: GetDigit = "Seven"
110.         Case 8: GetDigit = "Eight"
111.         Case 9: GetDigit = "Nine"
112.         Case Else: GetDigit = ""
113.     End Select
114. End Function