yen956

回復 5# liling81
試試VBA:
'========= 使用方法 ==============
'1. 按 Alt+F11 直接進入VBA編輯視窗(可避開「開發人員」的檢查)
'2. 插入→模組(Module1)
'3. 將下列兩個 Function 貼到 Module1
'4. 回到工作頁
'5. 假定 B2=伍萬陸仟柒佰捌拾元整
'6. 任一格 =CH2Num(B2)

1. '========= 將中文金額至千位轉成數值, 供下列 Function 呼叫用 ==============
   
2. Function CHS2Num(CH As String) As Integer
   
3.     Dim FD,  CN As String, Num As Integer
   
4.     CN = "壹貳參肆伍陸柒捌玖"
   
5.     Num = 0
   
6.     FD = Application.Find("仟", CH)
   
7.     If IsNumeric(FD) Then
   
8.         FD = Application.Find(Left(CH, 1), CN)
   
9.         If IsNumeric(FD) Then
   
10.             Num = Num + FD * 1000
    
11.         End If
    
12.     End If
    
13.     FD = Application.Find("佰", CH)
    
14.     If IsNumeric(FD) Then
    
15.         FD = Application.Find(Mid(CH, FD - 1, 1), CN)
    
16.         If IsNumeric(FD) Then
    
17.             Num = Num + FD * 100
    
18.         End If
    
19.     End If
    
20.     FD = Application.Find("拾", CH)
    
21.     If IsNumeric(FD) Then
    
22.         FD = Application.Find(Mid(CH, FD - 1, 1), CN)
    
23.         If IsNumeric(FD) Then
    
24.             Num = Num + FD * 10
    
25.         End If
    
26.     End If
    
27.     FD = Application.Find(Right(CH, 1), CN)
    
28.     If IsNumeric(FD) Then
    
29.         Num = Num + FD
    
30.     End If
    
31.     CHS2Num = Num
    
32. End Function
    
33. 
    
34. '========= 將中文金額至兆位轉成數值 ==============
    
35. Function CH2Num(CH As String) As Double
    
36.     Dim FD, CNunit As String, Num As Double
    
37.     Num = 0
    
38.     CNunit = "萬億兆"
    
39.     FD = Application.Find("兆", CH)
    
40.     If IsNumeric(FD) Then
    
41.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 12
    
42.         CH = Right(CH, Len(CH) - FD)
    
43.     End If
    
44.     FD = Application.Find("億", CH)
    
45.     If IsNumeric(FD) Then
    
46.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 8
    
47.         CH = Right(CH, Len(CH) - FD)
    
48.     End If
    
49.     FD = Application.Find("萬", CH)
    
50.     If IsNumeric(FD) Then
    
51.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 4
    
52.         CH = Right(CH, Len(CH) - FD)
    
53.     End If
    
54.     CH2Num = Num + CHS2Num(CH)
    
55. End Function

複製代碼