excel ±N¼Æ¦rÂà­^¤å¤j¼g

seci

§Ú¦bºô¤W¨ì³o­Ó¤èªk http://support.microsoft.com/kb/q213360/
¦ýÂର¤j¼gªº­^¤å¤º·|¦³dollars³o­Ó¦r, §Ú¬O¥Î¦b¦C¦L¤ä²¼¤W, ©Ò¥H¤£»Ý­n¦³"dollar"

example : Six Hundred Eighty Five Dollars and Thirty Three Cents

¦U¤j°ª¤â¦³¨S¦³¤èªk¦bexcel ±N¼Æ¦rÂà­^¤å¤j¼g ¦ý¨S¦³ "dollar"¦r©O ??/

Thanks.

chi830

¦^´_ 1# seci


    ¨ä¹ê¥u­n§â¥H¤U¦æ¼Æ¡A§R°£´N¦æ¤F
' Select Case Dollars
'        Case ""
'            Dollars = "No Dollars"
'        Case "One"
'            Dollars = "One Dollar"
'         Case Else
'            Dollars = Dollars & " Dollars"
'    End Select
'    Select Case Cents
'        Case ""
'            Cents = " and No Cents"
'        Case "One"
'            Cents = " and One Cent"
'              Case Else
'            Cents = " and " & Cents & " Cents"
'    End Select

C9212038

§Ú§â¨º¤@¦ê§R±¼«áÅܦ¨¤U­±ªº¼Ë¤l(513.51---¡÷ Five Hundred ThirteenFifty One)¡A¦ýAnd¤£¨£¤F¡AÁÙ¦³ThirteenÀ³¸Ó¬OªÅ¤@®æ¦A±µFifty¡A¥i²{¦b³s¦b¤@°_¤F¡C
¦pªGAnd«á­±·Q¥[Cents­n«ç»ò°µ©O¡HÁÂÁ±z¡I
§Ú·Q­nªº¬OFive Hundred Thirteen And Cents Fifty One

yen956

¦^´_ 3# C9212038
¸Õ¸Õ¬Ý:

1. Select Case Dollars
   
2.     Case ""
   
3. '        Dollars = "No Dollars"
   
4.         Dollars = ""
   
5.     Case "One"
   
6. '        Dollars = "One Dollar"
   
7.         Dollars = "One"
   
8.     Case Else
   
9. '        Dollars = Dollars & " Dollars"
   
10.         Dollars = Dollars & ""
    
11. End Select
    
12. 
    
13. Select Case Cents
    
14.     Case ""
    
15. '        Cents = " and No Cents"
    
16.         Cents = ""
    
17.     Case "One"
    
18.         'Five Hundred Thirteen And Cents Fifty One
    
19.         '¦pªG§Æ±æ¦p¤W Cents ©ñ«e­±,
    
20.         '¨º´N¤U¦æ§ï¦¨ Cents = " and Cent One"
    
21.         Cents = " and One Cent"
    
22.     Case Else
    
23.         '¦pªG§Æ±æ Cents ©ñ«e­±,
    
24.         '¨º´N¤U¦æ§ï¦¨ Cents = " and Cents " & Cents
    
25.         Cents = " and " & Cents & " Cents"
    
26. End Select

½Æ»s¥N½X

»¡©ú¡G«e­±¥[ ' ¨º¤@¦C¬Oµù¸Ñ, ¤£·|³QVBA°õ¦æ

­ã´£³¡ªL

µ{¦¡½X¤£¶·§ï¡A¦b¤½¦¡¤WµÛ¤â¡G
=SUBSTITUTE(¦Û­q¨ç¼Æ,"Dollars ","")

­ã´£³¡ªL

¡e©w¸q¦WºÙ+¤½¦¡¡f¼Æ¦rÂà­^¤å¤j¼g¡G
http://www.funp.net/246430

­Y¦³¿³½ì¡A¥i¦Û¦æ®M§ï¬Ý¬Ý¡ã¡ã

softsadwind

¦^´_ 1# seci

1. Option Explicit
   
2. 'Main Function
   
3. Function SpellNumber(ByVal MyNumber)
   
4. Dim Dollars, Cents, Temp
   
5. Dim DecimalPlace, Count
   
6. ReDim Place(9) As String
   
7. Place(2) = " Thousand "
   
8. Place(3) = " Million "
   
9. Place(4) = " Billion "
   
10. Place(5) = " Trillion "
    
11. ' ¥Nªí¼Æ¦rªº¦r¦ê¡C
    
12. MyNumber = Trim(Str(MyNumber))
    
13. ' ¦pªG­È¬°µL¡A«h¤Q¶i¦ì 0 ªº¦ì¸m¡C
    
14. DecimalPlace = InStr(MyNumber, ".")
    
15. ' ´«ºâ¤ÀµM«á±N MyNumber ªº¼Æ¦r³]¬°¤¸¡C
    
16. If DecimalPlace > 0 Then
    
17. Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    
18.                   "00", 2))
    
19. MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    
20. End If
    
21. Count = 1
    
22. Do While MyNumber <> ""
    
23. Temp = GetHundreds(Right(MyNumber, 3))
    
24. If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    
25. If Len(MyNumber) > 3 Then
    
26. MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    
27. Else
    
28. MyNumber = ""
    
29. End If
    
30. Count = Count + 1
    
31. Loop
    
32. Select Case Dollars
    
33. Case ""
    
34. Dollars = "No Dollars"
    
35. Case "One"
    
36. 'Dollars = "One Dollar"
    
37. Dollars = "One"
    
38. Case Else
    
39. 'Dollars = Dollars & " Dollars"
    
40. Dollars = Dollars
    
41. End Select
    
42. Select Case Cents
    
43. Case ""
    
44. Cents = " and No Cents"
    
45. Case "One"
    
46. Cents = " and One Cent"
    
47. Case Else
    
48. Cents = " and " & Cents & " Cents"
    
49. End Select
    
50. SpellNumber = Dollars & Cents
    
51. End Function
    
52.       
    
53. ' ±N¼Æ¦r 100-999 Âà´«¬°¤å¦r
    
54. Function GetHundreds(ByVal MyNumber)
    
55. Dim Result As String
    
56. If Val(MyNumber) = 0 Then Exit Function
    
57. MyNumber = Right("000" & MyNumber, 3)
    
58. ' ´«ºâ¦Ê¶i¦ìªº¡C
    
59. If Mid(MyNumber, 1, 1) <> "0" Then
    
60. Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    
61. End If
    
62. ' ´«ºâ¤Q¶i¦ì©M¤@¶i¦ì¡C
    
63. If Mid(MyNumber, 2, 1) <> "0" Then
    
64. Result = Result & GetTens(Mid(MyNumber, 2))
    
65. Else
    
66. Result = Result & GetDigit(Mid(MyNumber, 3))
    
67. End If
    
68. GetHundreds = Result
    
69. End Function
    
70.       
    
71. ' ±N¼Æ¦r 10 ¨ì 99 Âà´«¬°¤å¦r¡C
    
72. Function GetTens(TensText)
    
73. Dim Result As String
    
74. Result = ""           ' Null out the temporary function value.
    
75. If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
    
76. Select Case Val(TensText)
    
77. Case 10: Result = "Ten"
    
78. Case 11: Result = "Eleven"
    
79. Case 12: Result = "Twelve"
    
80. Case 13: Result = "Thirteen"
    
81. Case 14: Result = "Fourteen"
    
82. Case 15: Result = "Fifteen"
    
83. Case 16: Result = "Sixteen"
    
84. Case 17: Result = "Seventeen"
    
85. Case 18: Result = "Eighteen"
    
86. Case 19: Result = "Nineteen"
    
87. Case Else
    
88. End Select
    
89. Else                                 ' ¦pªG­È¤¶©ó 20-99...
    
90. Select Case Val(Left(TensText, 1))
    
91. Case 2: Result = "Twenty "
    
92. Case 3: Result = "Thirty "
    
93. Case 4: Result = "Forty "
    
94. Case 5: Result = "Fifty "
    
95. Case 6: Result = "Sixty "
    
96. Case 7: Result = "Seventy "
    
97. Case 8: Result = "Eighty "
    
98. Case 9: Result = "Ninety "
    
99. Case Else
    
100. End Select
     
101. Result = Result & GetDigit _
     
102. (Right(TensText, 1))  ' Retrieve ones place.
     
103. End If
     
104. GetTens = Result
     
105. End Function
     
106.      
     
107. ' ±N¼Æ¦r 1 ¨ì 9 Âà´«¬°¤å¦r¡C
     
108. Function GetDigit(Digit)
     
109. Select Case Val(Digit)
     
110. Case 1: GetDigit = "One"
     
111. Case 2: GetDigit = "Two"
     
112. Case 3: GetDigit = "Three"
     
113. Case 4: GetDigit = "Four"
     
114. Case 5: GetDigit = "Five"
     
115. Case 6: GetDigit = "Six"
     
116. Case 7: GetDigit = "Seven"
     
117. Case 8: GetDigit = "Eight"
     
118. Case 9: GetDigit = "Nine"
     
119. Case Else: GetDigit = ""
     
120. End Select
     
121. End Function

½Æ»s¥N½X

§ï±¼¨â­Ó¦a¤è´N¦n
Case "One"
'Dollars = "One Dollar" ­ì¥»ªº
Dollars = "One"
Case Else
'Dollars = Dollars & " Dollars" ­ì¥»ªº
Dollars = Dollars