![在 excel 2010 中,用查找值替换与模式对应的任意数字](https://linux22.com/image/1471288/%E5%9C%A8%20excel%202010%20%E4%B8%AD%EF%BC%8C%E7%94%A8%E6%9F%A5%E6%89%BE%E5%80%BC%E6%9B%BF%E6%8D%A2%E4%B8%8E%E6%A8%A1%E5%BC%8F%E5%AF%B9%E5%BA%94%E7%9A%84%E4%BB%BB%E6%84%8F%E6%95%B0%E5%AD%97.png)
我正在尝试找出一种方法,用另一个表中的相应值替换/替代/正则表达式替换 Excel 表中的特定模式。该模式在文本块中出现 x 次,始终遵循以下规则:
{[4,9]{1}[0-9]{5}}
(用大括号括起来,“4”或“9”后跟 5 个数字,每个数字可以是 0 到 9 之间的任意数字,例如“{412345}”或“{961723}”。)
然后,所需的公式应该去掉括号,在另一个表中查找数字,并从另一列返回相应的条目。
我已设法使用四个预定义的数字来实现此目的:
但是,我需要扩展公式以包括任何与模式匹配的数字。我想 VBA UDF 是可行的方法,尽管我不知道该怎么做……
附言:如果有人有兴趣进一步突破限制,下一步我要处理的是在花括号内的数字中添加 .X,其中 X 代表需要检查值的表。例如:“{412345.2}”表示“检查表 2 中的 412345 并替换为找到的值”,而“{912345.5}”应该是“检查表 5 中的 912345 并替换为找到的值”。
答案1
使用宏/VBA:
Public Function patSub(theValue As String) As String
result = theValue
Dim rango As Range
Set rango = ActiveSheet.Range("E1:F6")
strPattern = "{[4,9]{1}[0-9]{5}}"
Dim regEx As New RegExp
Dim matches
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
Set matches = regEx.Execute(theValue)
For Each Match In matches
On Error Resume Next
lookupmatch = Match.Value
lenMatch = Len(lookupmatch)
lookupmatch1 = Mid(lookupmatch, 2, lenMatch - 2)
lookupValue = Application.VLookup(lookupmatch1 + 0, rango, 2, Falso)
If lookupValue <> "Error 2042" Then
result = Replace(result, lookupmatch, lookupValue)
End If
Next
patSub = result
End Function
使用 alt+F11 打开 VBA /Macros,在下方插入一个新模块本工作簿并将代码粘贴到右侧。
制作常用表达使用 VBA 时需要参考Microsoft VBScript 正则表达式 5.5:
- 选择“工具/参考”
- 勾选Microsoft VBScript 正则表达式 5.5
- 点击“确定”
此 UDF 使用两个变量:
rango1
:包含项目的参考表。strPattern
:正则表达式。
如果数据在单元格中A1
,那么B1
您必须输入=patSub(A1)
。