在VBA中，如何删除文本字符串末尾以数字开头的内容？

Question

所以目前我的VBA代码如下所示：

Sub Testing()

Dim K As Long
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row


For K = 2 To LR
   Cells(K, 2).Value = StripAfter(Cells(K, 1), "_", 6)
Next K

End Sub


Function StripAfter(ByVal txt As String, ByVal delimiter As String, ByVal 
  occurrence As Long) As String
    Dim x As Variant
    x = Split(expression:=txt, delimiter:=delimiter, limit:=occurrence + 1)

    StripAfter = x(UBound(x))
End Function

有了这个输出，这正是公式所要做的，这是很棒的！我的问题是，对于B列中的结果，我不能全神贯注于此（我是VBA宏的新手，试图尽可能地学习），它们都以数字结尾，数字之间有X。我将如何调整我的代码，使其删除该部分文本？所以结果看起来像：

正如您从我要查找的结果中可以看到的，与给出的结果相比，###x##在末尾被删除。我在VBA之外玩了一圈，发现这是可行的，但它本质上是一个两步过程：

=RIGHT(SUBSTITUTE(A1,"_",CHAR(10),12),LEN(A1)-FIND(CHAR(10),SUBSTITUTE(A1,"_",CHAR(10),12),1)+1)

=LEFT(A20,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A20&"0123456789")) -1)

Answer 1

我也很难处理这样的公式，所以我使用了正则表达式，在函数中添加了几行。基本上是模式匹配。你的潜艇和以前一样。

Function StripAfter(ByVal txt As String, ByVal delimiter As String, ByVal occurrence As Long) As String

Dim x As Variant

x = Split(expression:=txt, delimiter:=delimiter, limit:=occurrence + 1)
StripAfter = x(UBound(x))

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+x\d+$"                                           'match 1+ numbers followed by x followed by 1+ numbers at the end of a string
    If .Test(StripAfter) Then StripAfter = .Replace(StripAfter, "") 'if pattern found replace with empty string
End With

End Function