|
楼主 |
发表于 2021-12-22 16:15
|
显示全部楼层
- Sub test_5()
- Dim arr1, arr2, arrRst$(), i&, j&, k&, b As Boolean, sTmp$
- t = Timer
- arr1 = Range("a2:a503")
- arr2 = [b1].Resize(, Cells(1, Columns.Count).End(1).Column - 1)
- ReDim arrRst(1 To UBound(arr1), 1 To UBound(arr2, 2))
- For i = 1 To UBound(arr1)
- For j = 1 To UBound(arr2, 2)
- b = False
- For k = 2 To Len(arr1(i, 1)) - 1
- sTmp = Mid(arr1(i, 1), k, 1)
- If Not IsNumeric(sTmp) Then If InStr(arr2(1, j), sTmp) Then b = True: Exit For 'instr比like效率高
- 'If Not IsNumeric(sTmp) Then If arr2(1, j) Like "*" & sTmp & "*" Then b = True: Exit For
- Next
- If b = False Then arrRst(i, j) = arr1(i, 1) & arr2(1, j)
- Next j
- Next i
- [b2].Resize(UBound(arrRst), UBound(arrRst, 2)) = arrRst
- MsgBox Timer - t
- End Sub
复制代码 在这代码那里替换,红色去掉字母和括号,提取数字
If b = False Then arrRst(i, j) = Replace(arr1(i, 1) ,“a-z,()”)& Replace(arr2(1, j),“a-z()”)
|
|