Excel VBA：删除指定字符前后内容但保留其左右各2个字符

Got it, let's break this down—wildcards in Find/Replace can't handle the "fixed number of characters around a target" logic you need, but VBA gives you full control to do exactly that. Here are a couple of flexible solutions depending on your use case:

1. Custom Worksheet Function (Easy for Cell-Level Use) #

This custom function lets you directly call it from Excel cells, just like a built-in function. It'll find your target character, then extract the n characters before and after it (you can adjust the number—your example uses 2).

Step 1: Add the VBA Code #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste this code into the module:

Function KeepAroundChar(inputText As String, targetChar As String, keepLength As Integer) As String
    ' Locate the first occurrence of the target character
    Dim charPos As Integer
    charPos = InStr(inputText, targetChar)
    
    ' Return original text if target character isn't found
    If charPos = 0 Then
        KeepAroundChar = inputText
        Exit Function
    End If
    
    ' Calculate start position (avoid going before the start of the text)
    Dim startPos As Integer
    startPos = IIf(charPos - keepLength < 1, 1, charPos - keepLength)
    
    ' Calculate end position (avoid going beyond the end of the text)
    Dim endPos As Integer
    endPos = IIf(charPos + keepLength > Len(inputText), Len(inputText), charPos + keepLength)
    
    ' Extract and return the desired substring
    KeepAroundChar = Mid(inputText, startPos, endPos - startPos + 1)
End Function

Step 2: Use the Function in Excel #

In any cell, enter the formula like this (adjust cell references and parameters as needed):

=KeepAroundChar(A1, "/", 2)

• A1: The cell with your original text
• "/": The target character you want to center on
• 2: Number of characters to keep on each side

Examples:

• Original: ......xx/yy.... → Result: xx/yy
• Original: /yyabc123 → Result: /yy (since there's nothing before the /)
• Original: abc123xx/ → Result: xx/ (since there's nothing after the /)

2. Batch Processing Macro (For Entire Columns/Ranges) #

If you need to process an entire column of data at once, use this macro to automate the task:

Sub BatchKeepAroundChar()
    Dim ws As Worksheet
    Set ws = ActiveSheet ' Replace with Sheets("YourSheetName") if needed
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Assumes data is in column A
    
    Dim i As Long
    For i = 1 To lastRow
        ' Skip empty cells
        If ws.Cells(i, "A").Value <> "" Then
            ' Output results to column B
            ws.Cells(i, "B").Value = KeepAroundChar(ws.Cells(i, "A").Value, "/", 2)
        End If
    Next i
End Sub

How to Run: #

1. Make sure you've already added the KeepAroundChar function from above.
2. Press F5 in the VBA Editor, or assign the macro to a button in Excel for easy access.

3. Bonus: Handle Multiple Occurrences of the Target Character #

If your text has multiple instances of the target character (e.g., abc/def/ghi), use this modified function to extract the characters around every occurrence:

Function KeepAllAroundChars(inputText As String, targetChar As String, keepLength As Integer) As String
    Dim charPos As Integer
    Dim result As String
    charPos = InStr(inputText, targetChar)
    
    ' Loop through all occurrences of the target character
    Do While charPos > 0
        Dim startPos As Integer
        startPos = IIf(charPos - keepLength < 1, 1, charPos - keepLength)
        
        Dim endPos As Integer
        endPos = IIf(charPos + keepLength > Len(inputText), Len(inputText), charPos + keepLength)
        
        ' Append the matched substring to the result (add a space between matches)
        result = result & Mid(inputText, startPos, endPos - startPos + 1) & " "
        
        ' Move past the current match to look for the next one
        inputText = Mid(inputText, endPos + 1)
        charPos = InStr(inputText, targetChar)
    Loop
    
    ' Remove trailing space before returning
    KeepAllAroundChars = Trim(result)
End Function

Example:

• Original: abc/def/ghi → Result: bc/de ef/gh

内容的提问来源于stack exchange，提问作者Iyas H