[已解决]提取单元格数据的VBA代码

网络人

求助提取单元个内所有英文字母到另外一个单元格的VBA代码

最佳答案

月排行榜 / 总排行榜

chart888

2017-8-15 19:57

1. Sub 提取()
   
2.     Dim regEx As Object
   
3.     Dim i As Integer
   
4.     Set regEx = CreateObject("VBSCRIPT.REGEXP")
   
5.     regEx.Pattern = "[^0-9.]"
   
6.     regEx.IgnoreCase = True
   
7.     regEx.Global = True
   
8.     For i = 1 To ActiveSheet.UsedRange.Rows.Count
   
9.         Cells(i, 4).Value = regEx.Replace(Cells(i, 2), "")
   
10.     Next i
    
11.     Set regEx = CreateObject("VBSCRIPT.REGEXP")
    
12.     regEx.Pattern = "[^a-zA-Z]"
    
13.     regEx.IgnoreCase = True
    
14.     regEx.Global = True
    
15.     For i = 1 To ActiveSheet.UsedRange.Rows.Count
    
16.         Cells(i, 3).Value = regEx.Replace(Cells(i, 2), "")
    
17.     Next i
    
18.     Set regEx = Nothing
    
19. End Sub
    

复制代码

正则我也是刚刚入门，哈哈

跳转到最佳答案楼层

chart888

1. Sub 提取()
   
2.     Dim regEx As Object
   
3.     Dim i As Integer
   
4.     Set regEx = CreateObject("VBSCRIPT.REGEXP")
   
5.     regEx.Pattern = "[^0-9.]"
   
6.     regEx.IgnoreCase = True
   
7.     regEx.Global = True
   
8.     For i = 1 To ActiveSheet.UsedRange.Rows.Count
   
9.         Cells(i, 4).Value = regEx.Replace(Cells(i, 2), "")
   
10.     Next i
    
11.     Set regEx = CreateObject("VBSCRIPT.REGEXP")
    
12.     regEx.Pattern = "[^a-zA-Z]"
    
13.     regEx.IgnoreCase = True
    
14.     regEx.Global = True
    
15.     For i = 1 To ActiveSheet.UsedRange.Rows.Count
    
16.         Cells(i, 3).Value = regEx.Replace(Cells(i, 2), "")
    
17.     Next i
    
18.     Set regEx = Nothing
    
19. End Sub
    

复制代码

正则我也是刚刚入门，哈哈