Excel VBA需求：移除数字后两字符间的指定字符串

Got it, let's tackle this Excel VBA problem you're facing. You mentioned needing to remove text that comes right after K/ (specifically when K/ is preceded by a number) up to a certain "specified position"—and that your previous attempts at adapting existing solutions didn't pan out. Let's build a solid, flexible solution together.

Step 1: The Reusable VBA Function #

First, open the VBA Editor by pressing Alt + F11, insert a new module, and paste this code:

Function RemoveTextAfterKslash(cell As Range) As String
    Dim originalText As String
    Dim kslashPos As Integer
    Dim endPos As Integer
    Dim resultText As String
    
    originalText = cell.Value
    resultText = originalText ' Fallback to original if no valid match is found
    
    ' Look for all occurrences of "K/" in the string
    kslashPos = InStr(1, originalText, "K/", vbTextCompare)
    Do While kslashPos > 0
        ' Only process if "K/" is preceded by a number
        If kslashPos > 1 And IsNumeric(Mid(originalText, kslashPos - 1, 1)) Then
            ' Define where to stop removing text (adjust this logic for your "specified position")
            ' --- Default logic: Stop at the first non-digit character after K/ ---
            endPos = kslashPos + 2 ' Start right after "K/"
            Do While endPos <= Len(originalText) And IsNumeric(Mid(originalText, endPos, 1))
                endPos = endPos + 1
            Loop
            
            ' --- Alternative logic: Stop at the first letter (uncomment if needed) ---
            ' endPos = kslashPos + 2
            ' Do While endPos <= Len(originalText) And Not IsLetter(Mid(originalText, endPos, 1))
            '     endPos = endPos + 1
            ' Loop
            
            ' Remove the target text segment
            resultText = Left(resultText, kslashPos - 1) & Mid(resultText, endPos)
            
            ' Check for any remaining "K/" occurrences in the modified string
            kslashPos = InStr(kslashPos, resultText, "K/", vbTextCompare)
        Else
            ' Skip this "K/" if it's not preceded by a number, move to next occurrence
            kslashPos = InStr(kslashPos + 2, originalText, "K/", vbTextCompare)
        End If
    Loop
    
    RemoveTextAfterKslash = resultText
End Function

' Helper function to check if a character is a letter (for the alternative logic)
Function IsLetter(char As String) As Boolean
    IsLetter = (UCase(char) >= "A" And UCase(char) <= "Z")
End Function

Step 2: How to Use the Function #

1. Return to your Excel worksheet.
2. In an empty cell, enter =RemoveTextAfterKslash(A1) (replace A1 with the cell holding your target text).
3. Drag the fill handle down to apply the function to other cells as needed.

Step 3: Customize for Your Exact "Specified Position" #

The code includes two ready-to-use options for defining where to stop removing text:

• Default active logic: Stops at the first non-digit character. For example, 456K/340kXYZ becomes 456KXYZ.
• Alternative logic: Stops at the first letter. Uncomment that section if your end condition is different (like stopping before the first alphabetical character).

You can easily tweak the endPos loop to match other conditions too—for example, if you need to stop at a space or a specific symbol, just adjust the loop's check to target that character.

Test Case Examples #

Why This Works #

• It scans for every instance of K/ and only processes those that follow a number, matching your specific requirement.
• The flexible endPos loop lets you define exactly where to stop stripping text, so you can adapt it to your unique use case.
• It handles multiple valid K/ occurrences in a single string if needed.

If you need help adjusting the end condition further (like targeting a specific symbol), just let me know!

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