Excel - 查找和替换多个单词 [英] Excel - Find and replace multiple words

问题描述

我只想简单地找到并替换多个字符串。例如，我需要将所有A1，A2，A3替换为系统，并将所有B1，B2替换为ACC等等...

有没有人知道要采取的好路线？我只是不知道如何开始。感谢您的帮助！

解决方案

更新在底部，Michael的评论是更好的方式进行许多模式替换

如果您使用手册从Excel菜单中替换选项记录一个简单的宏，您将获得代码可以整理到这个

1. 第一个选项将更新 ActiveSheet中的单元格包含我是A1到我是系统 - 零件字符串匹配


2. 第二个选项只会将 ActiveSheet 中仅包含A1的单元格更新为Sytem - 即整个单元格串匹配

strong>

  Sub UpdatePartial（）
 With ActiveSheet.UsedRange 
。替换A1，系统，xlPart 
。更改A2，系统，xlPart 
。替换A3，系统，xlPart 
。替换B1，ACC，xlPart 
。更改B2， ACC，xlPart 
 End with 
 End Sub 
 
 Sub UpdateWhole（）
 With ActiveSheet.UsedRange 
。替换A1，系统 ，xlWhole 
。更换A2，系统，xlWhole 
。替换A3，系统，xlWhole 
。更改B1，ACC，xlWhole 
。更改B2，ACC，xlWhole 
 End with 
 End Sub 
  

更新

以下代码

1. 使用基本的计时器来比较替换 A1-A99 之间的所有部分字符串 B1-B99


2. 这两种方法是
   
   
   
   • 替换方法在循环中调用198次（即2 * 99）
   
   
   • A RegExp \ variant array combo
   
   

在我的测试中，第二种方法比198个替换在1,000,000个单元格范围。

较少的替换将提高朝向替换的相对速度。更多的 RegExp
更多的单元格还将提高朝向替换的相对速度。 RegExp

我没有继续尝试 Find 方法，随后解析字符串。作为一个hyrbrid类型的解决方案（ find 然后，解析 ut不会对单个替换或解析

计时器

  Sub MainCaller ）
 Dim dbTime As Double 
 Dim lngCnt As Long 
 
 dbTime = Timer（）
对于lngCnt = 1 To 99 
调用UpdatePartial（A & lngCnt，System）
调用UpdatePartial（B& lngCnt，System）
 Next lngCnt 
 Debug.Print Timer（） -  dbTime 
 dbTime = Timer（）
调用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）Regexp - Variant Array Sub

  Sub RegexReplace（StrIn As String，StrOut As String）
 Dim rng1 As范围
 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 
'设置rng1 = Application.InputBox（为替换前导零选择范围，用户选择，Selection.Address， ，，8）
'如果rng1是Nothing然后退出Sub 
'出现错误GoTo 0 
 
 ActiveSheet.UsedRange 
设置rng1 = ActiveSheet.UsedRange 
 
'请参阅Patrick Matthews关于使用正则表达式与VBA 
的优秀文章Set objReg = CreateObject（vbscript.regexp）
与objReg 
 .Pattern = StrIn 
。 ignorecase = False 
 .Global = True 
结束
 
'通过关闭屏幕更新并将计算设置为手动
来加快代码禁用任何代码事件写入单元格时发生
应用程序
 lngCalc = .Calc ulation 
 .ScreenUpdating = False 
 .Calculation = xlCalculationManual 
 .EnableEvents = False 
结束
 
'测试用户所选范围内的每个区域
 
'使用SpecialCells定义特定单元格类型以在
上工作时，非连续范围区域是常见的每个rngArea在rng1.Areas 
'最常见的结果用于True结果优化代码速度
如果rngArea.Cells.Count> 1然后
'如果有多个单元格，则将变量数组设置为范围区域的尺寸
'使用Value2提供了比Value有用的速度改进。在我的测试中，空白单元格是2％，在非空白单元上高达10％
 X = rngArea.Value2 
对于lngRow = 1到rngArea.Rows.Count 
对于lngCol = 1到rngArea.Columns.Count 
'替换前导零
 X（lngRow，lngCol）= objReg.Replace（X（lngRow，lngCol），StrOut）
下一页lngCol 
下一页lngRow 
'将更新的数组转储回初始范围
 rngArea.Value2 = X 
 Else 
'适用于单个单元格范围区域。不需要变量数组
 rngArea.Value = objReg.Replace（rngArea.Value，StrOut）
结束If 
下一个rngArea 
 
'清理应用程序设置
应用程序
 .ScreenUpdating = True 
 .Calculation = lngCalc 
 .EnableEvents = True 
结束
 
设置objReg = Nothing 
结束Sub 
  

I just want to do a simple find and replace for multiple strings. For example, I need to replace all "A1", "A2", "A3" with "system" and all "B1", "B2" with "ACC" and so on...

Does anyone know a good route to take? I'm just not sure how to get this started. Thanks for the help!

解决方案

Update at bottom adressing Michael's comment re a better approach for many pattern replacements

If you record a simple macro using the manual Replace options from the Excel menu you will get code that you can tidy up to this

1. The first option will update a cell in the ActiveSheet than contains "I am A1" to "I am System" - a part string match
2. The second option will only update cells in the ActiveSheet that contains only "A1" to "Sytem" - ie a whole cell string match

code

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

Update

The code below

1. Uses a basic Timer to compare replacing all partial strings ranging from A1-A99 and B1-B99
2. The two methods are
   • The Replace method above called 198 times (ie 2*99) in a loop
   • A RegExp \ variant array combo

On my testing the second method is faster for the 198 replacements on a 1,000,000 cell range.

Less replacements will improve the relative speed towards the Replace. More towards the RegExp More cells will also improve the relative speed towards the Replace. Less towards the RegExp

I didn't proceed with trying a Find method with later parsing of strings. As a hyrbrid type solution (find then parse ut wouldn't be competetive to a single replace or parse)

Timer

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) Replace Sub

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

2) Regexp - Variant Array Sub

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

这篇关于Excel - 查找和替换多个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！