excel - VBA - 找到第二次出现的子字符串 "TL"并删除之后的所有内容

Question

它的作用:我有一段代码在单元格中找到一个子字符串 TL，并通过在“TL-”之后添加或删除 0 来强制其后面的数字长度为 6。 (即TL-00072 -> TL-000072、TL-034 -> TL-000034、TL-000000789 -> TL-000789)

我要它做什么:但是，有时一个单元格中有多个 TL 值。我需要查找是否第二次出现 TL，如果是，则删除第二次出现及其后的所有内容。

例子:

Start:                           Output:
TL-000789 TL-000187 TL-000773  ->   TL-000789
TL-000689 TL -000787           ->   TL-000689
TL-000982 TL - 980819          ->   TL-000982

这是我一直在使用 split 处理的代码(不正确且不工作)的尝试(也许 trim 也可以工作？)，它将找到 TL 的第二次出现并删除它之后的所有内容。下面的完整工作代码。

当前尝试代码

Dim splitValues As Variant
        If Str(str, "TL" + 1) 'do not know how to get SECOND occurrence
            splitValues = Split(theValue, "TL")
            theValue = splitValues(0)
        End If

工作代码

[将在开头添加新代码](问题顶部的解释)

注意:StartSht 是值和代码所在的工作簿。
所有被更改的值都在“C”列中

'force length of TL/CT to be 6/4 numbers long, eliminate spaces
    Dim str As String, ret As String, tmp As String, j As Integer, k As Integer
    For k = 2 To StartSht.Range("C2").End(xlDown).Row
        ret = ""
        str = StartSht.Range("C" & k).Value
        'for TL numbers

 ''''''''''''''new code goes here''''''''''''''''''

        If InStr(str, "TL") > 0 Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then ret = ret + tmp
            Next j
            'force to 6 numbers if too short; add 0s immediately after "TL-"
            For j = Len(ret) + 1 To 6
                ret = "0" & ret
            Next j
            'force to 6 numbers if too long; eliminate 0s immediately after "TL-"
            If Len(ret) > 6 Then
                Debug.Print Len(ret)
                For j = Len(ret) To 7 Step -1
                If Mid(ret, 1, 1) = "0" Then
                    ret = Right(ret, j - 1)
                End If
                Next j
            End If
            'eliminate superfluous spaces around "TL-"
            ret = "TL-" & ret
            StartSht.Range("C" & k).Value = ret
        'for CT numbers
        ElseIf InStr(str, "CT") > 0 Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then ret = ret + tmp
            Next j
        'force to 4 numbers if too short; add 0s immediately after "CT-"
        For j = Len(ret) + 1 To 4
            ret = "0" & ret
        Next j
        'force to 4 numbers if too long; eliminate 0s immediately after "CT-"
        If Len(ret) > 4 Then
            Debug.Print Len(ret)
            For j = Len(ret) To 5 Step -1
            If Mid(ret, 1, 1) = "0" Then
                ret = Right(ret, j - 1)
        End If
        Next j
    End If
    'eliminate superfluous spaces around "CT-"
    ret = "CT-" & ret
    StartSht.Range("C" & k).Value = ret
 End If
Next k

Answer 1

如果您找到第二个“TL”，您似乎可以重新定义 str 。行后:If InStr(str, "TL") > 0 Then添加另一个 IF 语句:If InStr(3, str, "TL") > 0 Then str = Mid(str, 1, Instr(3, str, "TL") - 2)
然后使用新的 str 继续执行其余代码.