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.