在Excel-VBA中使用RegEx替换文本 [英] Replace text using RegEx in Excel-VBA

问题描述

我在Excel中有如下数据(此处为一行-Excel中为一个单元格):

I have data in Excel like follows (one row here - one cell in Excel):

07 July 2015 12:02 – 14 July 2015 17:02
12 August 2015 22:02 – 01 September 2015 11:02

我想编写一个宏，该宏将删除用户选择的内容(多个单元格)中的所有时间信息(例如"12:02")，如下所示:

I want to write a macro that will delete all time info (e.g. "12:02") within a user's selection (multiple cells) to look like this:

07 July 2015 – 14 July 2015
12 August 2015 – 01 September 2015

当所有时间"都相似("00:00")时，此宏运行良好:

When all "times" where similar ("00:00") this macro worked perfectly:

Sub delete_time()     
    Selection.Replace What:="00:00", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

但是随后time-info不再统一，所以我决定使用RegEx.问题是我找不到在VBA上执行此操作的正确方法.我尝试了这个宏:

But then time-info stopped being uniform, so I decided to use RegEx. The problem is I can't find a proper way to do this on VBA. I tried this macro:

Sub delete_time()
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    On Error Resume Next

    RegEx.Global = True
    RegEx.Pattern = "\d\d\:\d\d"
    ActiveDocument.Range = _
        RegEx.Replace(ActiveDocument.Range, "")
End Sub

但是没有用.还尝试了"[0-9] {2}:[0-9] {2}"和"[0-9] [0-9]:[0-9] [0-9]"模式，但没有任何变化.因此，问题一定出在我对VBA的误解(我是新手).

But it didn't work. Also tried "[0-9]{2}:[0-9]{2}" and "[0-9][0-9]:[0-9][0-9]" patterns but nothing changed. So the problem must be in my misunderstanding of VBA (I'm new to it).

任何人都可以帮忙吗?

推荐答案

问题出在您的选择上.

ActiveDocument.Range = _
RegEx.Replace(ActiveDocument.Range, "")

ActiveDocument在Excel名称空间中不存在.我们有ActiveWorkbook或ThisWorkbook，但是现在您需要的是Selection.

ActiveDocument doesn't exist in the Excel namespace. We have ActiveWorkbook or ThisWorkbook, but what you need now is the Selection.

对每个循环使用a来迭代当前选择中的所有单元格，如下所示:

Use a for each loop to iterate all the cells in the current selection like this:

Dim myCell As Range

For Each myCell In Selection.Cells
  myCell.Value = RegEx.Replace(myCell.Value, "")
Next

这篇关于在Excel-VBA中使用RegEx替换文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！