Excel數(shù)字大寫轉(zhuǎn)換

1、1.阿拉伯?dāng)?shù)字轉(zhuǎn)換中文大寫。=IF(A7-INT(A7)=0,TEXT(A7,"DBNUM2")&"元整",IF(INT(A7*10)-A7*10=0,TEXT(INT(A7),"DBNUM2")&"元"&TEXT(INT(A7*10)-INT(A7)*10),"DBNUM2")&"角整",TEXT(INT(A7),"DBNUM2")&"元"&IF(INT(A7*10)-INT(A7)*1

2、0=0,"零",TEXT(INT(A7*10)-INT(A7)*10,"DBNUM2")&"角")&TEXT(RIGHT(A7,1),"DBNUM2")&"分")該函數(shù)中只需將A7換成你要轉(zhuǎn)換的數(shù)值所在單元格即可。（編寫SpellNumber實(shí)例函數(shù)）2.1 啟動(dòng) Microsoft Excel。 2.2 按 Alt+F11 啟動(dòng) Visual Basic 編輯器。 2.3 在“插入”菜單上，單擊“模塊”。 2.4 在模塊表中鍵入下面的代碼。Function Conver

3、tCurrencyToEnglish(ByVal MyNumber)          Dim Temp          Dim Dollars, Cents          Dim DecimalPlace, Count        

4、0; ReDim Place(9) As String          Place(2) = " Thousand "          Place(3) = " Million "          Place(4) = " Billion "  

5、;        Place(5) = " Trillion "          ' Convert MyNumber to a string, trimming extra spaces.          MyNumber = Trim(Str(Round(MyNumber, 2)   

6、;       ' Find decimal place.          DecimalPlace = InStr(MyNumber, ".")          ' If we find decimal place.        

7、60; If DecimalPlace > 0 Then             ' Convert cents             Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)      

8、       Cents = ConvertTens(Temp)             ' Strip off cents from remainder to convert.             MyNumber = Trim(Left(MyNumber, DecimalPlace

9、- 1)          End If          Count = 1          Do While MyNumber <> ""             '

10、; Convert last 3 digits of MyNumber to English dollars.             Temp = ConvertHundreds(Right(MyNumber, 3)             If Temp <> "" Then Dollars = Temp & Pl

11、ace(Count) & Dollars             If Len(MyNumber) > 3 Then                ' Remove last 3 converted digits from MyNumber.     

12、60;          MyNumber = Left(MyNumber, Len(MyNumber) - 3)             Else                MyNumber = "" &

13、#160;           End If             Count = Count + 1          Loop          ' Clean up dollars.&

14、#160;         Select Case Dollars             Case ""                Dollars = "No Dollars"  &#

15、160;          Case "One"                Dollars = "One Dollar"             Case Else  

16、0;             Dollars = Dollars & " Dollars"          End Select          ' Clean up cents.      

17、0;   Select Case Cents             Case ""                Cents = " Only"          &#

18、160;  Case "One"                Cents = " And One Cent"             Case Else          

19、60;     Cents = " And " & Cents & " Cents"          End Select          ConvertCurrencyToEnglish = Dollars & Cents       End Fun

20、ction      Private Function ConvertHundreds(ByVal MyNumber)          Dim Result As String          ' Exit if there is nothing to convert.       &#

21、160;  If Val(MyNumber) = 0 Then Exit Function          ' Append leading zeros to number.          MyNumber = Right("000" & MyNumber, 3)        

22、0; ' Do we have a hundreds place digit to convert?          If Left(MyNumber, 1) <> "0" Then             If Right("000" & MyNumber, 2) <> 0 Then  

23、;           Result = ConvertDigit(Left(MyNumber, 1) & " Hundred and "             Else             Result = ConvertD

24、igit(Left(MyNumber, 1) & " Hundred "             End If         End If          ' Do we have a tens place digit to convert?  &#

25、160;       If Mid(MyNumber, 2, 1) <> "0" Then             Result = Result & ConvertTens(Mid(MyNumber, 2)          Else    

26、60;        ' If not, then convert the ones place digit.             Result = Result & ConvertDigit(Mid(MyNumber, 3)          End If   

27、60;      ConvertHundreds = Trim(Result)       End Function       Private Function ConvertTens(ByVal MyTens)          Dim Result As String     

28、60;    ' Is value between 10 and 19?          If Val(Left(MyTens, 1) = 1 Then             Select Case Val(MyTens)          

29、60;     Case 10: Result = "Ten"                Case 11: Result = "Eleven"                Case 12: Result = &qu

30、ot;Twelve"                Case 13: Result = "Thirteen"                Case 14: Result = "Fourteen"    

31、60;           Case 15: Result = "Fifteen"                Case 16: Result = "Sixteen"            

32、;    Case 17: Result = "Seventeen"                Case 18: Result = "Eighteen"                Case 19: Result = &qu

33、ot;Nineteen"                Case Else             End Select          Else       

34、;      ' . otherwise it's between 20 and 99.             Select Case Val(Left(MyTens, 1)                Case 2: Result = "Twent

35、y"                Case 3: Result = "Thirty"                Case 4: Result = "Forty"       

36、         Case 5: Result = "Fifty"                Case 6: Result = "Sixty"                C

37、ase 7: Result = "Seventy"                Case 8: Result = "Eighty"                Case 9: Result = "Ninety"  &

38、#160;             Case Else             End Select             ' Convert ones place digit.    &#

39、160;        If Val(Right(MyTens, 1) = 0 Then             Result = Result & " " & ConvertDigit(Right(MyTens, 1)             Else

40、             Result = Result & "-" & ConvertDigit(Right(MyTens, 1)             End If         End If          ConvertTens = Result      End Function       Private Function ConvertDigit(ByVal MyDigit)          Select Case Val(MyDigit)    &