如何删除字符串任意位置指定字符及列单元格内指定格式子串？

Got it, let's break down these two common data-cleaning tasks step by step—perfect for tidying up spreadsheet data like your example set.

需求1：删除字符串任意位置的指定字符 #

Option 1: Excel Worksheet Formula #

If you prefer no code, use the SUBSTITUTE function. It replaces every instance of your target character with nothing:

=SUBSTITUTE(A1, "X", "")

Replace "X" with the character you want to delete. Need to remove multiple characters? Nest SUBSTITUTE calls:

=SUBSTITUTE(SUBSTITUTE(A1, "X", ""), "Y", "")

Option 2: VBA Custom Function #

For more flexibility (especially if you're reusing this across multiple sheets), create a custom function:

Function RemoveSpecificChar(inputStr As String, charToRemove As String) As String
    ' Replace all instances of the target character
    RemoveSpecificChar = Replace(inputStr, charToRemove, "")
End Function

To use it:

1. Open the VBA editor (Alt + F11)
2. Insert a new module
3. Paste the code above
4. Back in Excel, use =RemoveSpecificChar(A1, "X") in any cell

需求2：删除特定格式的子串（保留剩余内容） #

Your target patterns are a bit more complex, so regular expressions (RegEx) are the way to go. They let you match all those OS-related and number-plus-number patterns in one go.

Step 1: VBA Code with RegEx #

This custom function will scan your cell text, remove all the specified patterned substrings, and leave your desired text intact:

Function CleanPatternedText(inputStr As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    ' Define all patterns to match:
    ' - OS followed by 1-2 digits (with optional 1-2 decimal places)
    ' - 1-2 digits (with optional 1-2 decimal places) followed by OS
    ' - 1-6 digits + "+" + 2 digits
    regEx.Pattern = "(OS\d{1,2}(\.\d{1,2})?|\d{1,2}(\.\d{1,2})?OS|\d{1,6}\+\d{2})"
    regEx.Global = True ' Match all instances, not just the first
    regEx.IgnoreCase = False ' Case-sensitive (use True if you need case-insensitive)
    
    ' Replace matched patterns with empty string
    CleanPatternedText = Trim(regEx.Replace(inputStr, ""))
    
    ' Optional: Remove extra spaces left behind
    CleanPatternedText = Replace(CleanPatternedText, "  ", " ")
End Function

Step 2: How to Use It #

1. Follow the same VBA setup steps as above (open editor, insert module, paste code)
2. In Excel, use =CleanPatternedText(A1) on your target column
3. Drag the fill handle down to apply it to all cells

Example Result #

For your sample input:

38OS 15OS 1+00 15OS 10+00 15OS "text to keep" "text to keep" 15OS "text to keep" 23OS "text to keep" 1+00

The function will return:

"text to keep" "text to keep" "text to keep" "text to keep"

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