经验直达：
- excel中使用正则表达式
- 正则表达式如何匹配excel文件
一、excel中使用正则表达式
【正则表达式如何匹配excel文件 excel中使用正则表达式】Function JiSuan(Rng As Range) As Single
Dim Reg As New RegExp
Dim R As String
On Error GoTo ErrHand
R = (Rng)
Reg.Global = True
Reg.Pattern = "[u4e00-u9fa5]|m|M"
R = Reg.Replace(R, "")
Reg.Pattern = "×"
R = Reg.Replace(R, "*")
RegSZCC R
RegSZJJ R
JiSuan = Round(CSng(R), 4)
Set Reg = Nothing
Exit Function
ErrHand:
JiSuan = Err.Description
End Function
Sub RegSZCC(ByRef S As String)
Dim Reg As New RegExp
Dim MS
Dim R As String
Reg.Pattern = "(d .{0,1}d*)(*|\)(d .{0,1}d*)"
Set MS = Reg.Execute(S)
If MS.Count = 1 Then
If MS(0).SubMatches(1) = "" Then
R = Format(CSng(MS(0).SubMatches(0)) / CSng(MS(0).SubMatches(2)), "0.00000")
Else
R = Format(CSng(MS(0).SubMatches(0)) * CSng(MS(0).SubMatches(2)), "0.00000")
End If
S = Reg.Replace(S, R)
RegSZCC S
End If
End Sub
Sub RegSZJJ(ByRef S As String)
Dim Reg As New RegExp
Dim MS
Dim R As String
Reg.Pattern = "(d .{0,1}d*)( |-)(d .{0,1}d*)"
Set MS = Reg.Execute(S)
If MS.Count = 1 Then
If MS(0).SubMatches(1) = " " Then
R = Format(CSng(MS(0).SubMatches(0))CSng(MS(0).SubMatches(2)), "0.00000")
Else
R = Format(CSng(MS(0).SubMatches(0)) - CSng(MS(0).SubMatches(2)), "0.00000")
End If
S = Reg.Replace(S, R)
RegSZJJ S
End If
End Sub
EXCEL中调用方法
B1=jisuan(A1)
二、正则表达式如何匹配excel文件
Excel文件，常见格式有
*.xls;*.xl*;*.xla;*.xlt;*.xlm;*.xlc;*.xlw
使用正则表达式，可以用/.xl.{1,2}$/
下面着重看Excel2007 中的常用文件格式：
- *.xlsx：基于XML文件格式的Excel 2007工作簿缺省格式
- *.xlsm：基于XML且启用宏的Excel 2007工作簿
- *.xltx：Excel2007模板格式
- *.xltm：Excel 2007宏模板
- *.xlam：Excel 2007宏加载项
- *.xlsb：Excel2007为大的或复杂的工作簿新引入的非XML二进制文件格，允许优化执行和向后兼容 。
可以用另一个正则表达式，来精确匹配这6种格式
/.xl(s[xmb]|t[xm]|am)$/