正则提取AB到|之间的数字

hefuqing

帮忙看看正则提取AB到|之间的数字 的正则表达式怎么写

颜色:红/粉AB400|尺码:UN

颜色:000黑色AB000|尺码:UN

颜色:000蓝色AB607|尺码:L

颜色:蓝色AB645|尺码:000

iwangyi

=MID(A1,FIND("A",A1),FIND("|",A1)-FIND("A",A1))

iwangyi

附件---------

hefuqing

iwangyi 发表于 2018-8-25 15:22
=MID(A1,FIND("A",A1),FIND("|",A1)-FIND("A",A1))

正则表达式怎么写啊

jnsxmt

1. Sub test()
   
2.     Dim reg, m&, n&
   
3.     Set reg = CreateObject("vbscript.regexp")
   
4.     With reg
   
5.         .Global = True
   
6.         .Pattern = "AB\d+"
   
7.     End With
   
8.    
   
9.     For m = 1 To Cells(Rows.Count, "a").End(xlUp).Row
   
10.          Cells(m, "b") = reg.Execute(Cells(m, "a"))(0)
    
11.     Next
    
12. End Sub

复制代码

hefuqing

jnsxmt 发表于 2018-8-25 15:42

不要AB  只要数字 怎么写

惩戒骑

hefuqing 发表于 2018-8-25 15:48
不要AB  只要数字 怎么写

在5楼的基础上改了下，比较蠢，哈哈

1. Sub test()
   
2.     Dim reg, m&, n&
   
3.     Set reg = CreateObject("vbscript.regexp")
   
4.     With reg
   
5.         .Global = True
   
6.         .Pattern = "AB\d+"
   
7.     End With
   
8.    
   
9.     Set reg1 = CreateObject("vbscript.regexp")
   
10.     With reg1
    
11.         .Global = True
    
12.         .Pattern = "AB"
    
13.     End With
    
14.    
    
15.     For m = 1 To Cells(Rows.Count, "a").End(xlUp).Row
    
16.          Cells(m, "b") = Replace(reg.Execute(Cells(m, "a"))(0), reg1.Execute(Cells(m, "a"))(0), "")
    
17.     Next
    
18. End Sub

复制代码

hefuqing

惩戒骑 发表于 2018-8-25 15:52
在5楼的基础上改了下，比较蠢，哈哈

直接提取 怎么写

jnsxmt

1. Sub test()
   
2.     Dim reg, m&, n&, mH
   
3.     Set reg = CreateObject("vbscript.regexp")
   
4.     With reg
   
5.         .Global = True
   
6.         .Pattern = "AB(\d+)"
   
7.     End With
   
8.    
   
9.     For m = 1 To Cells(Rows.Count, "a").End(xlUp).Row
   
10.          For Each mH In reg.Execute(Cells(m, "a"))
    
11.             Cells(m, "b").NumberFormatLocal = "@"
    
12.             Cells(m, "b") = Format(mH.submatches(0), "000")
    
13.         Next
    
14.     Next
    
15.          
    
16.          
    
17. End Sub
    

复制代码

hopeson2010

1. Function yy(a As String) As Integer
   
2.    Dim dic As Object
   
3.       Application.Volatile
   
4.       Set dic = CreateObject("vbscript.regexp")
   
5.       With dic
   
6.           .Global = True
   
7.           .Pattern = "AB(\d+)\|"
   
8.           If .test(a) Then
   
9.               yy = .Execute(a)(0).submatches(0)
   
10.           End If
    
11.       End With
    
12. End Function
    

复制代码