Return Value from string if it Match is Found in Range

**Jonathan78** · 02-17-2015, 07:09 AM

I'm looking for a function to return the values from a string that exists in a Range.
If a match is found it should should be returned proceeded by a value 1 column next to the string.

The attachment will make clear what I mean.

**JOHN H. DAVIS** · 02-17-2015, 08:28 AM

Maybe:

Sub Jonathan78()
Dim w As String
Dim x As Long
Dim y As Long
Dim z As String
z = ""
For x = 24 To 27
w = Cells(x, "A") & "/"
    For y = 4 To 20
        If Cells(x, "B").Value Like "*" & Cells(y, "B").Value & "*" Then
            z = w & Cells(y, "B").Value & ","
        End If
        w = z
    Next y
Cells(x, "C") = w
Cells(x, "C").Replace ",", ", " & Cells(x, "A").Value & "/"
If Len(Cells(x, "C")) > 3 Then
Cells(x, "C") = Cells(x, "A") & "/" & Left(Cells(x, "C"), Len(Cells(x, "C")) - 5)
Else
Cells(x, "C") = Cells(x, "A") & "/" & Cells(x, "C")
End If
If Len(Cells(x, "C")) < 4 Then Cells(x, "C") = ""
z = ""
Next x
End Sub

**Jonathan78** · 02-17-2015, 08:58 AM

Thanks John, this seems to work.
How do I translate this in an Function?

**jindon** · 02-17-2015, 09:51 AM

H24:

=SplitList(A24,B24,$B$3:$B$20)

and fill down

Function SplitList(ByVal pref As String, txt As String, List As Range) As String
    Dim myList As String, m As Object
    myList = Join(Filter(Evaluate("transpose(if(" & List.Address(external:=True) & _
    "<>""""," & List.Address(external:=True) & ",char(2)))"), Chr(2), 0), "|")
    With CreateObject("VBScript.RegExp")
        .Pattern = myList: .Global = True
        For Each m In .Execute(txt)
            SplitList = SplitList & IIf(SplitList <> "", ", ", "") & pref & "/" & m.Value
        Next
    End With
End Function

**Jonathan78** · 02-17-2015, 10:05 AM

Thanks again Jindon, it works like a charm!

Return Value from string if it Match is Found in Range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Return Value from string if it Match is Found in Range

Re: Return Value from string if it Match is Found in Range

Re: Return Value from string if it Match is Found in Range

Re: Return Value from string if it Match is Found in Range

Re: Return Value from string if it Match is Found in Range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search for String in Range, If Match, Return Matching Cell Value

[SOLVED] IF range of cells contains string of text, return contents of cell where string is found

[SOLVED] Search for a string within a cell, if found, return value of 1

Excel 2007 : Return 1 or 0 if a string is found in a series of sheets

is it impossible to match 2 columns & return values from a 3rd when a match is found

Bookmarks

Bookmarks

Posting Permissions