带有以未知数字结尾的搜索字符串的 Vba excel instr 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26782253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Vba excel instr function with search string that ends with a unknown number
提问by Robin Ovaere
I want to search for a string that ends with a number but it is not known before what number it will be
我想搜索一个以数字结尾的字符串，但不知道它是什么数字
So I'm using the InStr function
所以我使用 InStr 函数
InStr(Range("B" & row).Value), "text")
But now the problem is, what i'm searching for can be "text0", "text1", "text9", I don't want to create 10 times a Instr function to test al the 10 numbers.
但现在的问题是，我要搜索的内容可以是“text0”、“text1”、“text9”，我不想创建 10 次 Instr 函数来测试 10 个数字。
What I'm looking for is a replacement character like you have # that stands for any given number in a input mask in Acces. So something like this
我正在寻找的是一个替换字符，就像你有 # 一样，它代表 Acces 中输入掩码中的任何给定数字。所以像这样
InStr(Range("B" & row).Value), "text" & #)
offcoarse this will not work as excel will take this as search for "text#" and wil not interpretate it as # is any given number.
offcoarse 这将不起作用，因为 excel 会将其视为对“text#”的搜索，并且不会将其解释为 # 是任何给定的数字。
Edit: Range("B" & row).Value will evaluate for example to "9S 279P3NOV/PDE NN1 PRS NO NVML"
编辑：Range("B" & row).Value 将评估为例如“9S 279P3NOV/PDE NN1 PRS NO NVML”
What i need to know is where NN1 is so I can extract it.
我需要知道的是 NN1 在哪里，以便我可以提取它。
But the next row can evaluate to "9S 2793NOV/PE NN12 REQ BANA" So again I need to know where NN12 is, also notice the text before NN12 changes and that NN now has 2 digits.
但是下一行可以评估为“9S 2793NOV/PE NN12 REQ BANA”所以我再次需要知道 NN12 在哪里，还要注意 NN12 更改之前的文本，并且 NN 现在有 2 位数字。
回答by Siddharth Rout
After reading the comments below the question
阅读问题下方的评论后
the number is random, the actual string i'm looking for always start with NN so the found string can be NN1, NN5 or actualy even NN25. There is no way in telling before what the number will be.
该数字是随机的，我要查找的实际字符串总是以 NN 开头，因此找到的字符串可以是 NN1、NN5 甚至 NN25。之前没有办法知道这个数字是多少。
Is this what you are trying? Use the LIKEwith wildcards.
这是你正在尝试的吗？使用LIKE通配符。
Try this
尝试这个
Sub Sample()
Dim stringToTest(1 To 5) As String
Dim i As Long
stringToTest(1) = "Test01"
stringToTest(2) = "Test01Test"
stringToTest(3) = "123"
stringToTest(4) = "01Test01"
stringToTest(5) = "NNature1234"
For i = 1 To 5
If stringToTest(i) Like "NN*#" Then Debug.Print stringToTest(i)
Next i
End Sub
Followup from comments / recent edit to the question
从评论/最近编辑对问题的跟进
If you format is going to as you have shown in the question, i.e there will be spaces then try this
如果您按照问题中显示的格式进行格式化，即会有空格，请尝试此操作
Sub Sample()
Dim s As String, stringToTest(1 To 2) As String
Dim ar
Dim i As Long, j As Long
stringToTest(1) = "9S 279P3NOV/PDE NN1 PRS NO NVML"
stringToTest(2) = "9S 2793NOV/PE NN12 REQ BANA"
For i = 1 To 2
s = stringToTest(i)
If s Like "*NN*#*" And InStr(1, s, " ") Then
ar = Split(s, " ")
For j = LBound(ar) To UBound(ar)
If ar(j) Like "NN*#" Then
Debug.Print ar(j)
Exit For
End If
Next j
End If
Next i
End Sub
Output
输出
NN1
NN12
回答by tm-
If I understood correctly, simple looping could help:
如果我理解正确，简单的循环可能会有所帮助：
Sub SearchNum()
Dim i As Integer
Dim strSource As String
Dim boolNumFound As Boolean
'Found flag
numFound = False
'Put source string to variable
'(put here your range address)
strSource = Range("B1").Value
'Loop through your number range
For i = 0 To 99
If InStr(1, strSource, "text" & i) Then
numFound = True
MsgBox "text" & i
Exit For
End If
Next i
End Sub
回答by mrbungle
I had a similar issue yesterday. I took the answer given to me and edited it to fit your issue, but I can't take 100% credit :-p. I believe this will get you what you're looking for.
我昨天遇到了类似的问题。我接受了给我的答案并对其进行了编辑以适应您的问题，但我不能 100% 相信 :-p。我相信这会让你得到你想要的。
sub test()
Dim sWords() As String
Dim s As Variant
Dim sResult As String
sWords = Split(ActiveCell.Value, " ")
For Each s In sWords
If Left$(s, 2) = "NN" Then
sResult = sResult & s
msgbox sResult
sResult = ""
End if
Next s
end sub
回答by Mark Main
I think this will run a bit faster than the other solutions provided. This is case-insensitive as written but removing the vbTextCompare would make it case-sensitive. I've tested this, the code works.
我认为这会比提供的其他解决方案运行得更快。这在写入时不区分大小写，但删除 vbTextCompare 将使其区分大小写。我已经测试过了，代码有效。
Function nnNumeric(ByVal textIn As String, Optional ByVal startPos As Long = 1) As Long
'searches textIn for NN followed by a number; e.g. NN0, NN1, NN2, etc.
'returns the position when found, otherwise returns #VALUE! error
Dim i As Long
i = InStr(startPos, textIn, "NN", vbTextCompare) 'remove vbTextCompare to make this case-sensitive
Do While i > 0
If IsNumeric(Mid(textIn, i + 2, 1)) Then
nnNumeric = i
Exit Function
End If
i = InStr(i + 1, textIn, "NN", vbTextCompare) 'remove vbTextCompare to make this case-sensitive
Loop
nnNumeric = CVErr(xlErrValue) '#VALUE! error
End Function
回答by PaulFrancis
Could this function work for you?
这个功能对你有用吗？
If IsNumeric(Right(Range("B" & row).Value, 1)) Then
MsgBox "It ends with a Number."
Else
MsgBox "It does not end with a Number."
End If
