vba Excel 公式：如果单元格包含子字符串“this”且不包含子字符串“that”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40498040/
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
Excel formula: If cell contains substring "this" AND does not contain substring "that"
提问by Marta
I'm trying to write a function in which a column contains one substring and does not contain another substring.
我正在尝试编写一个函数，其中一列包含一个子字符串但不包含另一个子字符串。
In the example bellow I would like my function to return 1 if my row contains "some project" AND DOES NOT CONTAIN "overhead".
在下面的示例中，如果我的行包含“某个项目”并且不包含“开销”，我希望我的函数返回 1。
row| example strings | desired return value
0 |some project,other project | 1
1 |some project | 1
2 |overhead | 0
3 |some project, overhead | 0
4 |some project, other, boo | 1
I was trying to formulate it first with exact strings such as:
我试图首先用确切的字符串来制定它，例如：
=IF(AND((E3="some project"),NOT(E3="overhead")),1,0)
But this only gives correct results for row 1 and 2 because it only does exact mach for the string instead of matching on the substring.
但这只会为第 1 行和第 2 行提供正确的结果，因为它只对字符串执行精确匹配，而不是对子字符串进行匹配。
回答by wbrugato
What you need is some kind of Substring function. I think FINDmight work. Check out this page: https://exceljet.net/excel-functions/excel-find-function
您需要的是某种 Substring 函数。我认为FIND可能会奏效。查看此页面：https: //exceljet.net/excel-functions/excel-find-function
Your function would be like:
=IF(AND(ISERROR(FIND("some project", E3))=FALSE,ISERROR(FIND("overhead",E3))),1,0)
你的功能是这样的：
=IF(AND(ISERROR(FIND("some project", E3))=FALSE,ISERROR(FIND("overhead",E3))),1,0)
EDIT: Above function works after testing
编辑：以上功能在测试后有效
Tricky part here is that FINDreturns the starting position of the string, and if it fails it returns #VALUE, which I believe you can catch with the ISERROR()function. This is in no way a beautiful solution. I would try to utilize the code behind and write this is VBA, as I am certain there is a proper substringfunction in VBA.
这里棘手的部分是FIND返回字符串的起始位置，如果失败则返回#VALUE，我相信您可以使用该ISERROR()函数捕获它。这绝不是一个漂亮的解决方案。我会尝试利用后面的代码并将其写成 VBA，因为我确信substringVBA 中有适当的功能。
回答by David Zemens
If you can insert a little VBA code, then you can use a custom function like so:
如果您可以插入一些 VBA 代码，那么您可以使用这样的自定义函数：
=StrContains(E3, "some project", "overhead")
And this will return Trueif the value in E3 contains bothof those substrings. This function relies mainly on VBA's Instrfunction, which
True如果 E3 中的值包含这两个子字符串，这将返回。这个函数主要依赖VBA的Instr函数，它
Function code:
功能代码：
Public Function StrContains(ByRef cl As Excel.Range, ParamArray strings() As Variant)
'Function returns TRUE if the range contains ALL of the passed substring items
' uses ParamArray to allow varying number of substring items
' Ex:
' =StrContains(A1, "something", "else", "foo")
'
Dim val$
Dim s
Dim i As Integer
Dim ret As Boolean
Dim length As Integer
length = UBound(strings) + 1
val = cl.Value2
For Each s In strings
If InStr(1,val, s) <> 0 Then
i = i + 1
End If
Next
ret = (i = length)
StrContains = ret
End Function
You could modify this relatively easily to be case-insensitive, or to accept partial matches optionally, etc. Here is what it looks like extended for both of those concepts:
您可以相对轻松地将其修改为不区分大小写，或可选地接受部分匹配等。以下是对这两个概念的扩展：
=StrContains(E3, False, True, "some project", "overhead")
Function Code:
功能代码：
Public Function StrContains(ByRef cl As Excel.Range, MatchCase As Boolean, MatchAll as Boolean, ParamArray strings() As Variant)
'MatchAll is matching switch, use True to require ALL matching items, or False to allow for fewer.
'MatchCase is the Case-sensitive switch, use False to ignore case.
' uses ParamArray to allow varying number of substring items
' Ex:
' =StrContains(A1, "something", "else", "foo")
'
Dim val$
Dim s
Dim i As Integer
Dim ret As Boolean
Dim length As Integer
length = UBound(strings) + 1
val = cl.Value2
If Not MatchCase Then
val = LCase(val)
For i = lBound(strings) to UBound(strings)
strings(i) = lcase(strings(i))
Next
Next
For Each s In strings
If InStr(val, s) <> 0 Then
i = i + 1
End If
Next
ret = (i = IIF(MatchAll, length, 1))
StrContains = ret
End Function
回答by kajahno
You can actually use the SEARCH function, like this:
您实际上可以使用 SEARCH 函数，如下所示：
=IF(ISNUMBER(SEARCH("some project",B2)),NOT(ISNUMBER(SEARCH("overhead",B2)))*1,0)
Considerations:
注意事项：
- SEARCHfunction: returns either a number or error (that's why I used ISNUMBER, you could actually have used ISERRORtoo). This function is case insensitive. For case sensitiveness you might just use FIND, in the previous formula woud be
=IF(ISNUMBER(FIND("some project",B2)),NOT(ISNUMBER(FIND("overhead",B2)))*1,0) - ISNUMBERfunction: returns either FALSE or TRUE. I converted the result to 1 or 0 simply by multiplying TRUE or FALSE by 1.
- SEARCH函数：返回数字或错误（这就是我使用 ISNUMBER 的原因，您实际上也可以使用ISERROR）。此函数不区分大小写。为了区分大小写，您可能只使用FIND，在前面的公式中
=IF(ISNUMBER(FIND("some project",B2)),NOT(ISNUMBER(FIND("overhead",B2)))*1,0) - ISNUMBER函数：返回 FALSE 或 TRUE。我只是通过将 TRUE 或 FALSE 乘以 1 将结果转换为 1 或 0。
Hope helps
希望有帮助
