想向大神们请教一下提取字串的方法,我想用VBA码,将A列以下AWPX字串后的数字提取到H列,A的资料还会向下增加。
当中会用到IFERROR,MID,FIND
请帮忙修改一下,万分感谢
Dim sht As Worksheet Dim k As Long Set sht = ThisWorkbook.Worksheets("sheet1") k = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, 1), Cells(k, 1)) Set Rng2 = Range(Cells(1, 8), Cells(k, 8)) Rng2.Formula = "=IFERROR(MID(Rng,Find(""AWPX"",Rng)+7,2)"0")"
本帖最后由 苏子龙 于 2017-8-20 11:04 编辑
- Sub tt()
- Dim reg, i&, arr, brr
- arr = Sheets("工作表1").Range("a1", Range("a65536").End(3))
- ReDim brr(1 To UBound(arr))
- Set reg = CreateObject("VBScript.RegExp")
- reg.Global = True
- reg.IgnoreCase = True
- reg.Pattern = "awpx.+?(\d+)"
- For i = 1 To UBound(arr)
- If reg.Test(arr(i, 1)) Then
- brr(i) = reg.Execute(arr(i, 1))(0).SubMatches(0)
- Else
- brr(i) = "no"
- End If
- Next
- Sheets("工作表1").Range("d1").Resize(UBound(brr)) = Application.Transpose(brr)
- End Sub
复制代码
|