查找，删除单元格 [英] Find, delete in cell

问题描述

大家好，

我有一本具有很多信息的excel工作簿.我想做的是在一个单元格中找到字符串，然后删除该字符串和接下来的5个数字字符.

在字符串和数字字符之间有一个空格.我可以找到字符串，但是后来我不知道如何删除该字符串和接下来的5个数字.重要的一点是，在一个单元格中它可以出现多个相同的字符串格式，因此我无法删除所有整个单元格


字符串格式为:Word 12345


提前致谢！ :)

Hi all,

I have an excel workbook with lot of information. What I''m trying to do is to find string inside a cell, then deleting that string and next 5 number characters.

Between string and number chars it has one space. I can find string, but then I don''t know how to delete that string and next 5 numbers. One important thing is that in one cell it can appear more than one same string format, so i can''t delete all entire cell


String format is: Word 12345


thanks in advance! :)

推荐答案

尝试类似的方法:

Try something like this:

Option Explicit

'required reference to MS VBScript Regular Expression 5.5
Sub RemoveMatches()
Dim wsh As Worksheet, oRegEx As VBScript_RegExp_55.RegExp 
Dim i As Integer, sTmp As String, sPattern As String

On Error GoTo Err_RemoveMatches

Set wsh = ThisWorkbook.Worksheets(1)

sPattern = "\W*([a-zA-Z]{1,} [0-9]{5})"
i = 1
Do While wsh.Range("A" & i) <> ""
    sTmp = wsh.Range("A" & i)
    Set oRegEx = New VBScript_RegExp_55.RegExp
    With oRegEx
        .Pattern = sPattern
        .Global = False
        .IgnoreCase = True
        .MultiLine = False
        'find "Word 12345" and replace with empty string
        sTmp = .Replace(sTmp, "")
    End With
    'insert new value
    wsh.Range("A" & i) = sTmp
    Set oRegEx = Nothing
    i = i + 1
Loop

Exit_RemoveMatches:
    On Error Resume Next
    Set wsh = Nothing
    Set oRegEx = Nothing
    Exit Sub

Err_RemoveMatches:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_RemoveMatches

End Sub

这篇关于查找，删除单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！