如果选择包含单词，则返回指定文本的if函数 [英] An if function that returns specified text if the selection contains a word

问题描述

我需要在excel中创建一个返回Text1的函数（或者更简单的宏），如果选择CONTAINSSpecified Text1，或者如果它包含Specified Text2，则返回Text2，如果它包含Specified Text3然后返回一个值Text3。

I need to create a function (or macro, which ever is easier) in excel that returns "Text1" if a selection CONTAINS "Specified Text1" or if it contains "Specified Text2" then return "Text2" and if it contains "Specified Text3" then return a value of "Text3".

推荐答案

进一步澄清我在你的评论中喜欢你的问题问：

Till further clarification i asume your question to like in my comment asked:

如果我让你对，你有abc，def和ghi在hstdefhrk中搜索，而有123，456和789作为输出=>你想得到456...是正确的？

if i got you right, you have "abc, "def" and "ghi" to search for in "hstdefhrk" while having "123", "456" and "789" as output => you want to get "456"... is that correct?

通过公式得到这个结果：

To get this by formula:

'case sensitive
=INDEX({"";"OutputText1";"OutputText2";"OutputText3"},MAX(ROW($1:$4)*ISNUMBER(FIND({"";"SearchText1";"SearchText2";"SearchText3"},A1))))

'not case sensitive
=INDEX({"";"OutputText1";"OutputText2";"OutputText3"},MAX(ROW($1:$4)*ISNUMBER(SEARCH({"";"SearchText1";"SearchText2";"SearchText3"},A1))))

是一个数组公式，必须用 Ctrl + Shift + 输入。

为了做UDF，请尝试这样：（不区分大小写）

For doing it as an UDF try this: (not case sensitive)

Public Function multiSearch(sourceStr As String, srcMatrix As Variant, dstMatrix As Variant, Optional caseSen As Boolean) As String
  Dim runner As Variant, i As Long
  For Each runner In srcMatrix
    If InStr(1, sourceStr, runner, caseSen + 1) Then Exit For
    i = i + 1
  Next
  For Each runner In dstMatrix
    If i = 0 Then
      multiSearch = runner
      Exit For
    Else
      i = i - 1
    End If
  Next
End Function

使用它像 LOOKUP

=multiSearch([striong to search in],[array of strings to search for],[array of strings to output],[1/true for case sensitive])

=multiSearch(A1,{"SearchText1";"SearchText2";"SearchText3"},{"OutputText1";"OutputText2";"OutputText3"}) 'not case sensitive
=multiSearch(A1,D1:D6,E1:E6,1) 'case sensitive

这篇关于如果选择包含单词，则返回指定文本的if函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！