Find First Substring in a String

Question

I have a short user defined function that, given a string and a set of possible sub-strings in a column, will return the position of the first substring found.

I have been asked to replace the UDF with a non-VBA formula.

Option Explicit
Public Function Locate(s As String, rng As Range) As Variant
    Dim pos As Long, p As Long, cell As Range
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    Locate = CVErr(xlErrNA)
    pos = 999999

    For Each cell In rng
        p = InStr(s, cell.Text)
        If p > 0 Then
            pos = wf.Min(pos, p)
        End If
    Next cell

    If pos <> 999999 Then Locate = pos
End Function

Here is an example:

The substrings are in column F. The function finds both b and q and returns 5 becuase q comes first.

I have a formula that works for a specific instance:

=MIN(FIND({"a","b","c","d","q"},A1 & "abcdq",1))

I have been trying to find a way to replace the {"a","b","c","d","q"} and "abcdq" with a reference to a range of cells, but I am stumped.

Answer 1

use CONCAT:

=MIN(FIND(F1:F5,A1&CONCAT(F1:F5),1))