check for specific word and give result

**saravanan1981** · 11-22-2015, 10:39 PM

I have a workbook with two sheets Data and URM

In sheet "URM" in column F ("URM") I have details of URM

In sheet "Data" in Column "E"(Remarks) I have sentences.

I wants to check that whether any of the sentences in "E" in Sheet "Data" contains the URM from the sheet "URM"
Some senetence in Remaks might contains more than one URM. In that case extract all the URM and separate with "^". Example given in attachment marked in green colour
URM might me part of sentence in Remakrs column delimited by space and other symbol or without space also.

So have to look for exactly as in URM column

If URM found in next Column(Column"F") in sheet "Data" show the same URM. if not found shouw result as "NO URM"

**protonLeah** · 11-23-2015, 12:41 AM

................

**jindon** · 11-23-2015, 01:45 AM

saravanan1981

What happened to this

http://www.excelforum.com/excel-prog...our-parts.html

For this one.

Sub test()
    Dim myPtn As String, a, i As Long, m As Object
    myPtn = Join(Application.Transpose(Sheets("urm").Range("g1").CurrentRegion _
            .Columns(1).Offset(1).SpecialCells(2).Value), Chr(2))
    With Sheets("data")
        With .Range("e2", .Range("e" & Rows.Count).End(xlUp)).Resize(, 2)
            .Columns(2).ClearContents: a = .Value
            With CreateObject("VBScript.RegExp")
                .Global = True: .IgnoreCase = True
                .Pattern = "([\$\(\)\-\^\|\\\{\}\[\]\+\*\.\?])"
                myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
                .Pattern = myPtn
                For i = 1 To UBound(a, 1)
                    If Not .test(a(i, 1)) Then
                        a(i, 2) = "NO URM"
                    Else
                        For Each m In .Execute(a(i, 1))
                            a(i, 2) = a(i, 2) & IIf(a(i, 2) <> "", "^", "") & m.Value
                        Next
                    End If
                Next
            End With
            .Value = a
        End With
    End With
End Sub

**saravanan1981** · 11-26-2015, 02:38 PM

hi Jindon,

sorry for the delay in responding. thank you for your code.

Please find the attachment, it shows some error. Please refer cell F5. fg is matching in Sheet URM and also cell E5 contains the same. But still code shows some error.

please help

**jindon** · 11-26-2015, 07:17 PM

You should respond to the link first.

**saravanan1981** · 12-01-2015, 03:43 PM

also need one more addition in this code. please refer highlighted points below and refer attachment

I have a workbook with two sheets Data and UTR

In sheet "UTR" in column F ("UTR") I have details of UTR

In sheet "Data" in Column "E"(Remarks) I have sentences.

I wants to check that whether any of the sentences in "E" in Sheet "Data" contains the UTR from the sheet "UTR"
Some sentence in Remarks might contains more than one UTR. In that case extract all the UTR and separate with "^". Example given in attachment marked in green colour
UTR might me part of sentence in Remarks column delimited by space and other symbol or without space also.

So have to look for exactly as in UTR column

If UTR found in next Column (Column"F") in sheet "Data" show the same UTR. if not found show result as "NO UTR"

Also in some cases single Cell might contain more than one UTR. In that case pull all the UTR and separate with “^”

Cell e-42 contains 3 utr in same cell. Hence in F 42 all three UTR has been extracted