- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
本帖最後由 yen956 於 2015-12-25 11:53 編輯
回復 5# liling81
試試VBA:
'========= 使用方法 ==============
'1. 按 Alt+F11 直接進入VBA編輯視窗(可避開「開發人員」的檢查)
'2. 插入→模組(Module1)
'3. 將下列兩個 Function 貼到 Module1
'4. 回到工作頁
'5. 假定 B2=伍萬陸仟柒佰捌拾元整
'6. 任一格 =CH2Num(B2)- '========= 將中文金額至千位轉成數值, 供下列 Function 呼叫用 ==============
- Function CHS2Num(CH As String) As Integer
- Dim FD, CN As String, Num As Integer
- CN = "壹貳參肆伍陸柒捌玖"
- Num = 0
- FD = Application.Find("仟", CH)
- If IsNumeric(FD) Then
- FD = Application.Find(Left(CH, 1), CN)
- If IsNumeric(FD) Then
- Num = Num + FD * 1000
- End If
- End If
- FD = Application.Find("佰", CH)
- If IsNumeric(FD) Then
- FD = Application.Find(Mid(CH, FD - 1, 1), CN)
- If IsNumeric(FD) Then
- Num = Num + FD * 100
- End If
- End If
- FD = Application.Find("拾", CH)
- If IsNumeric(FD) Then
- FD = Application.Find(Mid(CH, FD - 1, 1), CN)
- If IsNumeric(FD) Then
- Num = Num + FD * 10
- End If
- End If
- FD = Application.Find(Right(CH, 1), CN)
- If IsNumeric(FD) Then
- Num = Num + FD
- End If
- CHS2Num = Num
- End Function
- '========= 將中文金額至兆位轉成數值 ==============
- Function CH2Num(CH As String) As Double
- Dim FD, CNunit As String, Num As Double
- Num = 0
- CNunit = "萬億兆"
- FD = Application.Find("兆", CH)
- If IsNumeric(FD) Then
- Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 12
- CH = Right(CH, Len(CH) - FD)
- End If
- FD = Application.Find("億", CH)
- If IsNumeric(FD) Then
- Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 8
- CH = Right(CH, Len(CH) - FD)
- End If
- FD = Application.Find("萬", CH)
- If IsNumeric(FD) Then
- Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 4
- CH = Right(CH, Len(CH) - FD)
- End If
- CH2Num = Num + CHS2Num(CH)
- End Function
|
|
發表於 2015-12-25 11:49
| 