|
- Sub 结算表型号提取()
- Dim Reg, Arr, x%, mat, ma, Brr, Crr, y&, z&
- Application.ScreenUpdating = False
- Set Reg = CreateObject("Vbscript.RegExp")
- With Reg
- .Global = True
- '.Pattern = "((?!zy美的)[a-zA-Z0-9\-]+)((?!美的)[\u4e00-\u9fa5]+)"
- .Pattern = "([\u4e00-\u9fa5]+)([a-zA-Z0-9\-]+)([\u4e00-\u9fa5]+)?"
- End With
- Arr = Range("a1:k" & Cells(Rows.Count, 3).End(3).Row)
- Brr = Array("美的", "西施", "白玉瓷", "玲珑", "智能")
- Crr = Array("MB-", "MD-")
- For x = 2 To UBound(Arr)
- Set mat = Reg.Execute(Arr(x, 5))
- For Each ma In mat
- Arr(x, 9) = ma.submatches(0)
- Arr(x, 10) = ma.submatches(1)
- Arr(x, 11) = ma.submatches(2)
- Next ma
- Next x
- For y = 0 To UBound(Brr)
- For x = 2 To UBound(Arr)
- If Arr(x, 9) = "" Then Arr(x, 9) = Arr(x, 11)
- If InStr(Arr(x, 9), Brr(y)) Then Arr(x, 9) = Replace(Arr(x, 9), Brr(y), "")
- Next x
- Next y
- For z = 0 To UBound(Crr)
- For x = 2 To UBound(Arr)
- If InStr(Arr(x, 10), Crr(z)) Then Arr(x, 10) = Replace(Arr(x, 10), Crr(z), "")
- Next x
- Next z
- Range("a1").Resize(UBound(Arr), UBound(Arr, 2)) = Arr
- Range("i1").Resize(1, 3) = Array("中文", "英文", "辅助列")
- Sheet1.UsedRange.Borders.LineStyle = 1
- Application.ScreenUpdating = True
- End Sub
复制代码 求助理由:
1、因为从系统导出的产品型号极其不规范,需进行整理以便后期统计分析使用;
2、以上代码综合运用正则,instr和replace函数,为本人所写,限于个人水平,
勉强能达到目标,但自我感觉通用性不强,后期维护估计会耗费较多时间;
3、请问是否可以用更为精准的正则表达式,而不结合使用instr和replace函数实现目标?
|
|