Excel - 查找并替换多个单词

Question

我只想对多个字符串进行简单的查找和替换。例如，我需要将所有的"A1"、"A2"、"A3"替换为"system"，所有的"B1"、"B2"替换为"ACC"等等...

有人知道一个好的方法吗？我不确定如何开始。谢谢您的帮助！

- user960358

---

"A2" 是字符串还是单元格引用？ - brettdj

Answer 1

在底部更新，解决了Michael的评论，提供了更好的处理多个模式替换的方法

如果您使用Excel菜单中的手动Replace选项记录一个简单的宏，您将得到可以整理为以下代码的代码

1. 第一个选项将会更新ActiveSheet中包含"I am A1"的单元格，使其变为"I am System"——部分字符串匹配
2. 第二个选项仅会更新ActiveSheet中只包含"A1"的单元格，使其变为"Sytem"——即整个单元格字符串匹配

代码

Sub UpdatePartial()
With ActiveSheet.UsedRange
.Replace "A1", "System", xlPart
.Replace "A2", "System", xlPart
.Replace "A3", "System", xlPart
.Replace "B1", "ACC", xlPart
.Replace "B2", "ACC", xlPart
End With
End Sub

Sub UpdateWhole()
With ActiveSheet.UsedRange
.Replace "A1", "System", xlWhole
.Replace "A2", "System", xlWhole
.Replace "A3", "System", xlWhole
.Replace "B1", "ACC", xlWhole
.Replace "B2", "ACC", xlWhole
End With
End Sub

更新

以下代码：

1. 使用基本的Timer来比较替换所有部分字符串，范围从A1-A99和B1-B99
2. 这两种方法是：
   • 上面的Replace方法在循环中被调用了198次（即2*99）
   • 一个RegExp \变体数组组合

在我的测试中，第二种方法对于在1,000,000个单元格范围内进行198次替换更快。

较少的替换将改善相对于Replace的速度。更多的替换将改善相对于RegExp的速度。 更多的单元格也将改善相对于Replace的速度。较少的单元格将改善相对于RegExp的速度。

我没有尝试过使用后期解析字符串的Find方法。作为混合类型的解决方案（查找然后解析），它不会与单一的替换或解析相竞争。

计时器

Sub MainCaller()
Dim dbTime As Double
Dim lngCnt As Long

dbTime = Timer()
For lngCnt = 1 To 99
Call UpdatePartial("A" & lngCnt, "System")
Call UpdatePartial("B" & lngCnt, "System")
Next lngCnt
Debug.Print Timer() - dbTime
dbTime = Timer()
Call RegexReplace("(A|B)[1-99]", "System")
Debug.Print Timer() - dbTime
End Sub

1) 替换子串

Sub UpdatePartial(StrIn As String, StrOut As String)
ActiveSheet.UsedRange.Replace StrIn, StrOut, xlPart
End Sub    

2) 正则表达式 - 变体数组子

Sub RegexReplace(StrIn As String, StrOut As String)
    Dim rng1 As Range
    Dim rngArea As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim objReg As Object
    Dim X()


    'On Error Resume Next
    'Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
    'If rng1 Is Nothing Then Exit Sub
    'On Error GoTo 0

    ActiveSheet.UsedRange
    Set rng1 = ActiveSheet.UsedRange

    'See Patrick Matthews excellent article on using Regular Expressions with VBA
    Set objReg = CreateObject("vbscript.regexp")
    With objReg
    .Pattern = StrIn
    .ignorecase = False
    .Global = True
    End With

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'replace the leading zeroes
                    X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), StrOut)
                Next lngCol
            Next lngRow
            'Dump the updated array back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            rngArea.Value = objReg.Replace(rngArea.Value, StrOut)
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

    Set objReg = Nothing
End Sub

- brettdj

---

如果我只想在一列中更新它，我该怎么办？我可以使用类似于Columns("C:C") with activesheet这样的东西吗？ - user960358

你可以使用 With ActiveSheet.Columns("A:A") 而不是 ActiveSheet.UsedRange。但在运行之前，你应该通过 xl 菜单将查找和替换选项设置为“worksheet”，而不是“workbook”。 - brettdj

如果您使用正则表达式，您只需要编写两个替换语句。而不是A1、A2、A3，您可以搜索A[0-9]{1,2}。这将找到A1-A99。B也是如此：B[0-9]{1,2}。这样，如果数据集变得更大/更小，您就不必永远编写替换语句。 - tmoore82

@tmoore82 是的。我同意你的观点，对于数百个基于模式的替换，可能需要一种新的方法。 :)例如，使用“查找”和“查找下一个”来定位所有潜在的替换字符串，然后进行“Like”测试（考虑到模式的简单性，正则表达式可能过于复杂）是一个不错的选择。选项取决于可能找到多少匹配项。或者将UsedRange转储到变体数组中，然后使用Regexp可能更有意义。 - brettdj

@brettdj 太棒了！将不同的方法排列在一起非常有用。感谢您的指导！ :) - tmoore82