从 Excel 范围中提取唯一子字符串

Question

我有一些单元格值，它们的名称彼此非常接近。我想提取一个共同的子字符串。

这是一个例子。

1100_250_Jump_12HR_100MD_S_run1 
1100_250_Jump_12HR_100MD_S_run2
1100_250_Jump_12HR_100MD_S_run3 
1100_250_Jump_12HR_100MD_S_run4 
1101_250_Jump_12HR_100MD_U_run5 
1101_250_Jump_12HR_100MD_U_run6 
1102_250_Jump_12HR_100MD_U_run7 
1102_250_Jump_12HR_100MD_U_run8 
1102_250_Jump_12HR_100MD_U_run9 
1102_250_Jump_12HR_100MD_U_run10

输出应该是_250_Jump_12HR_100MD_

如何在 Excel 中提取公共子字符串？

我检查了类似的问题，但找不到答案。

Answer 1

根据我在评论中发布的链接中的 UDF：

Option Explicit


Public Function CSTMatch3(Target1 As Range, Target2 As Range) As String

CSTMatch3 = ""

Dim myString As String, String1 As String, String2 As String, i As Long, j As Long, noChar As Long

noChar = 0

'The goal here is to assign the larger String to the variable String1
If Target1 = Target2 Then
    CSTMatch3 = Target1
    Exit Function
End If

If Len(Target1) >= Len(Target2) Then
    String1 = Target1
    String2 = Target2
Else
    String1 = Target2
    String2 = Target1
End If

For j = 1 To Len(String2)
    For i = 1 To Len(String1) - j
        If InStr(String2, Mid(String1, i, j)) Then
            myString = Mid(String1, i, j)
            noChar = noChar + 1
            Exit For
        End If
    Next i
Next j

CSTMatch3 = myString

End Function

然后，假设你的字符串在列中，像这样A使用 UDF ：B1

=CSTMatch3(A1,A2)

然后在B2：

=CSTMatch3(A2,B1)

并向下填充。

现在，任意给定行中的列中的单元格都会显示该行之前B所有单元格的通用字符串。A