检查一个字符串是否包含另一个字符串

使用 Instr 函数（此处找到的旧版本的 MSDN 文档)

Dim pos As Integer

pos = InStr("find the comma, in the string", ",")

将在 POS 中返回 15

如果未找到，它将返回 0

如果您需要使用 excel 公式查找逗号，您可以使用该函数。=FIND(",";A1)

请注意，如果要用于查找不区分大小写的字符串的位置，请使用 Instr 的第三个参数并赋予它 const（或者对于顽固分子来说只有 1）。InstrvbTextCompare

Dim posOf_A As Integer

posOf_A = InStr(1, "find the comma, in the string", "A", vbTextCompare)

将给你一个值 14。

请注意，在这种情况下，您必须按照我链接的规范中所述指定起始位置：如果指定了 compare，则需要 start 参数。

还有 InStrRev 函数，它执行相同类型的操作，但从文本的末尾到开头开始搜索。

根据@rene的回答...

Dim pos As Integer
pos = InStrRev("find the comma, in the string", ",")

...仍将 15 返回给 POS，但如果字符串具有多个搜索字符串，例如单词“the”，则：

Dim pos As Integer
pos = InStrRev("find the comma, in the string", "the")

...将返回 20 个到 pos，而不是 6。

基于 Rene 的答案，您还可以编写一个函数，如果子字符串存在，则返回 TRUE，如果不存在，则返回 FALSE：

Public Function Contains(strBaseString As String, strSearchTerm As String) As Boolean
'Purpose: Returns TRUE if one string exists within another
On Error GoTo ErrorMessage
    Contains = InStr(strBaseString, strSearchTerm)
Exit Function
ErrorMessage:
MsgBox "The database has generated an error. Please contact the database administrator, quoting the following error message: '" & Err.Description & "'", vbCritical, "Database Error"
End
End Function

您也可以使用特殊词：like

Public Sub Search()
  If "My Big String with, in the middle" Like "*,*" Then
    Debug.Print ("Found ','")
  End If
End Sub

鉴于现有的 Instr/InstrRev 函数，您真的不想这样做，但有时使用 EVALUATE 在 VBA 中返回 Excel 工作表函数的结果很方便

Option Explicit

Public Sub test()

    Debug.Print ContainsSubString("bc", "abc,d")

End Sub
Public Function ContainsSubString(ByVal substring As String, ByVal testString As String) As Boolean
    'substring = string to test for; testString = string to search
    ContainsSubString = Evaluate("=ISNUMBER(FIND(" & Chr$(34) & substring & Chr$(34) & ", " & Chr$(34) & testString & Chr$(34) & "))")

End Function

为了完成列出的可能性，我想演示如何将 Split（） 用于具有以下变体的全能函数，具体取决于传递的可选参数：n

• a） 显示是否找到了子字符串（-1 或省略默认值）
• b） 显示找到的子字符串数量 （0） ，
• c） 显示在哪个位置找到第 n 个子字符串 （1 .. n）。

Function StrIncludes( _
      ByVal s As String, _
      Optional ByVal IncludeString As String = ",", _
      Optional n As Long = -1 _
    ) As Long
'Purp.: find specified substring based on numeric value n
'Note : 2nd argument IncludeString is optional (default value is comma if omitted)
'       3rd argument n:  -1~~>only boolean; 0~~>count(s); 1..n ~~>position
    Dim tmp: tmp = Split(s, IncludeString)
    StrIncludes = UBound(tmp) > 0           ' a) boolean return value indicating a found substring
    
    Select Case n                           ' individual numeric values:
        Case 0                              ' b) return Count(s), not boolean value
            StrIncludes = UBound(tmp)
        Case 1
            StrIncludes = IIf(StrIncludes, Len(tmp(n - 1)) + n, 0)
        Case Is > 1                        ' c) return Position of nth finding
            If n > UBound(tmp) Then StrIncludes = 0: Exit Function
            StrIncludes = IIf(StrIncludes, Len(tmp(0)) + n, 0)
            Dim i As Long
            For i = 2 To n: StrIncludes = StrIncludes + Len(tmp(i - 1)): Next
    End Select
End Function

调用示例

Sub ExampleCall()
'   define base string
    Dim s As String
    s = "Take this example string, does it contain a comma, doesn't it?"
'a) check if base string contains indicated search string, e.g. a comma (default value)
    Debug.Print "Is Found: " & CBool(StrIncludes(s)) ' ~~> Is Found: True
'b) get number of substrings
    Debug.Print "Count(s): " & StrIncludes(s, , 0)   ' ~~> Count(s): 2
'c) get position of nth substring
    Debug.Print "~~~ Findings of nth substring ~~~ "
    Dim n As Long
    For n = 1 To 3
        Debug.Print n & ordinalSuffix(n) & " substring at Pos.:  " & StrIncludes(s, , n)
    Next
End Sub

Function ordinalSuffix(ByVal number As Long) As String
    Dim suffixes: suffixes = Split(" st nd rd th")
    ordinalSuffix = suffixes(Abs(number))
End Function

即时窗口中的调试结果

Is Found: Wahr
Count(s): 2
~~~ Findings of nth substring ~~~ 
1st substring at Pos.:  25
2nd substring at Pos.:  50
3rd substring at Pos.:  0  ' no finding at all