|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
昨天，交流群有兄弟提到用VAL不能把只有数字的字符串转换成数值的问题，一看就知道字符串里有不可见的字符导致转换失败，于是说应该用正则来提取。个人来说，正则写得不怎么样，所以都是把一些常用的记录到我的笔记里的，于是我又贴了我的笔记出来，但今天早上，那兄弟突然问我怎样把我的代码可以应用起来，（有点点纳闷……）
现在修改了一下，做成一个实例给各位初学者参考，至于哪些正则高手就不要来耻笑了哈，但可以提出更好的建议或者做法或者纠正指正，先谢谢了。
- Sub 正则提取应用()
- Dim vData As Variant, sStr As String, vNum As Variant, nI As Integer
-
- sStr = "ab知道(12.6-3.4)收到"
- vData = 正则提取(sStr, 11) '得到各数字形式的字符串
- ReDim vNum(1 To UBound(vData))
- For nI = 1 To UBound(vData)
- vNum(nI) = Val(vData(nI)) '把数字形式字符串转换成数值
- Next
- End Sub
- Private Function 正则提取(ByVal sStr As String, nType As Integer) As Variant
- 'nType 提取的是第几种类型
- Dim oData As Object, oItem As Object, vData As Variant, nData As Integer
- Dim regExp As Object
- Dim vPattern As Variant, sPattern As String
- Dim vReplace As Variant
- sPattern = "|" & "[0-9]" '1、单个纯数字为关键字
- sPattern = sPattern & "|" & "[0-9]+" '2、多个纯数字为关键字
- sPattern = sPattern & "|" & "\d" '3、单个纯数字为关键字
- sPattern = sPattern & "|" & "\d+" '4、多个纯数字为关键字
- sPattern = sPattern & "|" & "[^0-9]" '5、单个非数字为关键字
- sPattern = sPattern & "|" & "[\u4e00-\u9fa5]" '6、单个汉字为关键字
- sPattern = sPattern & "|" & "[\u4e00-\u9fa5]+" '7、多个个汉字为关键字
- sPattern = sPattern & "|" & "[^\u4e00-\u9fa5]" '8、单个非汉字为关键字
- sPattern = sPattern & "|" & "\D+" '9、汉字，字母，符号等为关键字
- sPattern = sPattern & "|" & "[^a-zA-Z]" '10、汉字，数字，符号等为关键字
- sPattern = sPattern & "|" & "\d+(?:\.\d+)?" '11、数值形式（含小数）为关键字
- sPattern = sPattern & "|" & "(\d+(?:\.\d+)?-\d+(?:\.\d+)?)" '12、(数值-数值) 形式为关键字，结果不含括号
- sPattern = sPattern & "|" & "\(\d+(?:\.\d+)?-\d+(?:\.\d+)?\)" '13、(数值-数值) 形式为关键字，结果含括号
- sPattern = sPattern & "|" & "工\d+(?:\.\d+)?" '14、工字开头+数值形式（含小数）为关键字
- sPattern = sPattern & "|" & "[\u5de5]\d+(?:\.\d+)?" '15、工字开头+数值形式（含小数）为关键字，注意前面中括号内是工字的UniCode码
- vPattern = Split(sPattern, "|") '分离出各种功能语句
- ReDim vReplace(1 To UBound(vPattern) + 1) '清空关键字的结果保存
- vReplace(UBound(vReplace)) = "[^|]+" '用|作为分离对象
-
- Set regExp = CreateObject("Vbscript.RegExp")
- With regExp
- .Global = True '设置全局可用性。 决定是匹配全局！！还是匹配首字位！
- .IgnoreCase = True '忽略字母大小写
- If nType > 0 And nType <= UBound(vPattern) Then
- .Pattern = vPattern(nType) '正则分离语句
- vReplace(nType) = .Replace(sStr, "") '对字符串进行关键字类型内容去掉
- Set oData = .Execute(sStr) '对字符串进行以关键字类型内容进行提取成对象
- If oData.Count > 0 Then
- ReDim vData(1 To oData.Count)
- nData = 0
- For Each oItem In oData
- nData = nData + 1
- vData(nData) = oItem
- Next
- End If
- ElseIf nType = 0 Then
- '针对拆分(??.?-??.?)拆分里面数值
- .Pattern = "\((\d+(?:\.\d+)?)-(\d+(?:\.\d+)?)\)"
- Set oData = .Execute(sStr)
- With oData(0)
- ReDim vData(1 To .SubMatches.Count)
- For nData = 1 To .SubMatches.Count
- vData(nData) = .SubMatches(nData - 1) '获取子匹配
- Next
- End With
- Else
- vData = Empty '说明超出上面已经固化下来的提取类型
- End If
- End With
- 正则提取 = vData
- End Function
|
评分
-
4
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-8-24 12:50
楼主
