Excel 正则表达式提取值

缺乏、安全感 2022-06-17 07:11 246阅读 0赞

快捷键 Alt +F11 打开代码视图

**![Center][]**

代码：

Function GetStr(rng As Range)

With CreateObject("VBscript.regexp")

.Global = True

.Pattern = "\\d+\\\*\\d+\\+\{0,1\}\\d\{0,\}"    '表达式

If .Execute(rng).Count = 0 Then

GetStr = ""

Else

GetStr = .Execute(rng)(0)

End If

End With

End Function

函数 GetStr 就编写完成了，使用方法截图如下：

本例是从源数据中提取字符串

![Center 1][]

测试数据如下：

<table style="margin: 0px; border-collapse: collapse; border: 1px solid rgb(187, 187, 187); outline: none; width: 348pt;">    
 <tbody> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border: 1px solid rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none; width: 348pt;">60*112mm, ,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">无品牌,Kurio X Screen Protector,130*74mm,无保护贴,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">75*115mm, ,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">190*255mm, ,装iPad保护贴,胶袋,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">无品牌,Kurio 7S Screen Protector,193*120mm,无保护贴,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">180*95mm,平口袋,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">61*115mm, ,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">65*120mm, ,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">135*76mm, ,1308touch5胶袋。,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">65*130mm, ,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">143*74（开口方向）mm,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">120*95mm, ,开口95mm,胶袋</td> 
  </tr> 
  <tr style="height: 13.5pt;"> 
   <td style="padding: 4px 8px; border-collapse: collapse; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: rgb(187, 187, 187); border-bottom-color: rgb(187, 187, 187); border-left-color: rgb(187, 187, 187); position: relative; height: 13.5pt; word-break: break-all; box-sizing: border-box; outline: none;">75*130+25mm, ,胶袋</td> 
  </tr> 
 </tbody> 
</table>

[Center]: /images/20220617/2ccfd94377dc45bd8d3c8f004a8ab8f5.png
[Center 1]: /images/20220617/0dee0bd01849447a8ec20d681989aaa1.png