An if function that returns specified text if the

Question

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".

Answer 1

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

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))))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

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

And use it like 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

Answer 2

I am going to assume that the actual strings are not "Specified Text" and some number. That they are in fact separate unique strings.

You could use nested if statements:

=IF(NOT(ISERROR(SEARCH("Specified Text1",A1))),"Text1",IF(NOT(ISERROR(SEARCH("Specified Text2",A1))),"Text2",IF(NOT(ISERROR(SEARCH("Specified Text3",A1))),"Text3","")))

Answer 3

Try,

=IFERROR(MID(A1, SEARCH("specified ", A1)+10, LEN(A1)), "")