我想查找一个字符串中是否包含了 ","(逗号)。除了逐个字符读取之外,我们有其他选项吗?
使用 Instr 函数(旧版本的 MSDN 文档可在 此处 找到)。
Dim pos As Integer
pos = InStr("find the comma, in the string", ",")
会在位置pos返回15
如果未找到,则返回0
如果您需要使用Excel公式查找逗号,可以使用=FIND("&","A1")
函数。
请注意,如果要使用Instr
不区分大小写地查找字符串的位置,请使用Instr的第三个参数并将其设为常量vbTextCompare
(或者只需使用1即可)。
Dim posOf_A As Integer
posOf_A = InStr(1, "find the comma, in the string", "A", vbTextCompare)
此时你需要像我链接的规范中所述一样指定起始位置,如果指定了比较参数,则需要提供起始参数。
这将为您提供一个值为14。
您还可以使用特殊单词like
:
Public Sub Search()
If "My Big String with, in the middle" Like "*,*" Then
Debug.Print ("Found ','")
End If
End Sub
还有一个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:
建立在 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
尽管已经有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
的以下变体:
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
INSTR
对您有效吗? - Stephen Quan