|
- Option Explicit
- Sub Demo()
- Dim i As Long, j As Long, n As Long
- Dim strTxt As String
- Dim arrData, aTxt, arrRes, dataRng As Range
- Dim objRegExp As Object, objMatch As Object
- Set dataRng = [B4].CurrentRegion
- arrData = dataRng.Value
- ReDim arrRes(1 To UBound(arrData), 0 To 2)
- n = 0
- Set objRegExp = CreateObject("vbScript.Regexp")
- With objRegExp
- .Global = True
- .MultiLine = True
- .Pattern = "([A-Z]*)(\d{6})(?:.*?([AB]\d))*"
- .IgnoreCase = True
- For i = 1 To UBound(arrData)
- strTxt = arrData(i, 1)
- Set objMatch = objRegExp.Execute(strTxt)
- If objMatch.Count > 0 Then
- n = n + 1
- For j = 0 To objMatch(0).submatches.Count - 1
- arrRes(n, j) = objMatch(0).submatches(j)
- If IsNumeric(arrRes(n, j)) And Len(arrRes(n, j)) > 0 Then _
- arrRes(n, j) = "'" & arrRes(n, j)
- Next
- End If
- Next
- End With
- With dataRng.Offset(, 3).Resize(, 3)
- .Value = arrRes
- With .Columns(1)
- .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
- .Value = .Value
- End With
- With .Columns(3)
- .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
- .Value = .Value
- End With
- End With
- Set objRegExp = Nothing
- Set objMatch = Nothing
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|