这是一个老问题，但对于使用Excel 2016或更新版本的人来说，解决方案是使用新的IFS(condition1, returnn1 [，condition2, return2]…)条件来消除嵌套if结构的需求。

我已经格式化了它，使它在视觉上更清楚地说明如何使用它来解决这个问题:

=IFS(
ISERROR(SEARCH("String1",A1))=FALSE,"Something1",
ISERROR(SEARCH("String2",A1))=FALSE,"Something2",
ISERROR(SEARCH("String3",A1))=FALSE,"Something3"
)

由于SEARCH如果没有找到字符串就会返回错误，所以我用ISERROR(…)=FALSE来检查是否正确，然后返回所需的值。如果SEARCH为可读性返回0而不是错误，那就太好了，但不幸的是，这就是它的工作方式。

Another note of importance is that IFS will return the match that it finds first and thus ordering is important. For example if my strings were Surf, Surfing, Surfs as String1,String2,String3 above and my cells string was Surfing it would match on the first term instead of the second because of the substring being Surf. Thus common denominators need to be last in the list. My IFS would need to be ordered Surfing, Surfs, Surf to work correctly (swapping Surfing and Surfs would also work in this simple example), but Surf would need to be last.

这是一个老问题，但对于使用Excel 2016或更新版本的人来说，解决方案是使用新的IFS(condition1, returnn1 [，condition2, return2]…)条件来消除嵌套if结构的需求。

我已经格式化了它，使它在视觉上更清楚地说明如何使用它来解决这个问题:

=IFS(
ISERROR(SEARCH("String1",A1))=FALSE,"Something1",
ISERROR(SEARCH("String2",A1))=FALSE,"Something2",
ISERROR(SEARCH("String3",A1))=FALSE,"Something3"
)

由于SEARCH如果没有找到字符串就会返回错误，所以我用ISERROR(…)=FALSE来检查是否正确，然后返回所需的值。如果SEARCH为可读性返回0而不是错误，那就太好了，但不幸的是，这就是它的工作方式。

Another note of importance is that IFS will return the match that it finds first and thus ordering is important. For example if my strings were Surf, Surfing, Surfs as String1,String2,String3 above and my cells string was Surfing it would match on the first term instead of the second because of the substring being Surf. Thus common denominators need to be last in the list. My IFS would need to be ordered Surfing, Surfs, Surf to work correctly (swapping Surfing and Surfs would also work in this simple example), but Surf would need to be last.

对于那些希望在IF语句中使用单个函数来完成此操作的人，我使用

=IF(COUNTIF(A1,"*TEXT*"),TrueValue,FalseValue)

查看子字符串TEXT是否在单元格A1中

[注意:TEXT需要带星号]

有趣的*

=COUNT(MATCH("*SomeText*",A1,))
=COUNTA(VLOOKUP("*SomeText*",A1,1,))
=COUNTA(HLOOKUP("*SomeText*",A1,1,))

如果“SomeText”包含在A1中，则返回1。

这是一个老问题，但我认为它仍然有效。

既然没有CONTAINS函数，为什么不在VBA中声明它呢? 下面的代码使用VBA Instr函数，它在字符串中查找子字符串。如果没有找到该字符串，则返回0。

Public Function CONTAINS(TextString As String, SubString As String) As Integer
    CONTAINS = InStr(1, TextString, SubString)
End Function

下面的公式确定文本“CHECK”是否出现在单元格C10中。如果没有，则结果为空白。如果是这样，结果就是工作“CHECK”。

=IF(ISERROR(FIND("CHECK",C10,1)),"","CHECK")