CODEWITHSUNDEEP
excelvbaworksheet-function
Edward
Edward

Reputation: 11

In Excel how do you a find a selection of text that exists within a list of text?

Another excel question following some great help I got on a similar query, say i have some text which is part of a URL say:

example-text

then the output of the formula i want finds in a long list of urls whether 'example-text' occurs and then if does returns the full url from the list that was searched ? say:

www.lalala/example-text/lala/random.com

Would be great if someone could solve this for me ?

added:

oh i see, thanks again. yeah I dont think I explained it well enough, i'll have another stab: say i have a long list of urls i want to be able to see whether "example-text" occurs in the url's, then if so the forumla output itslef is the url that contains "example-text".. is that doable ? thanks. Edward

Upvotes: 0

Views: 891

Answers (2)

Excellll
Excellll

Reputation: 5785

Here's a VBA user-defined function you can use to return the first item in a list that contains your search text. Just enter this code in a new module in the VBA editor window (Alt+F11 to open VBA editor).

Public Function FIRSTWITHIN(lookfor As String, lookin As Variant) As String

Dim tmparray() As Variant
Dim firstmatch As String
firstmatch = "No matching URL found."

'Feeds lookin into temporary array.  Error Handler handles case where lookin is a range.
'This allows use of function in array formula.
On Error GoTo ErrHandler
tmparray = lookin

'Looks through array column by column for match.  Returns first match found.
For j = 1 To UBound(tmparray, 2)
    For i = 1 To UBound(tmparray, 1)
        If InStr(tmparray(i, j), lookfor) > 0 Then
            firstmatch = tmparray(i, j)
            Exit For
        End If
    Next i
    If firstmatch <> "No matching URL found." Then
        Exit For
    End If
Next j
FIRSTWITHIN = firstmatch
Exit Function

ErrHandler:
    tmparray = lookin.Value
    Resume Next

End Function

You can then use the function as follows:

=FIRSTWITHIN("example-text",A2:A20)

where A2:A20 is the list of URLs you want to search through.

Upvotes: 1

JMax
JMax

Reputation: 26601

Try the find doc function:

=IF(ISERROR(FIND(A2,A1,1)),"Not in the url",A1)

(assuming your url is in A1 and the example-text in cell A2

Upvotes: 1

Related Questions