Search for a word from a sentence....

**raghuprasad.999** · 08-24-2012, 05:32 AM

Hi Experts,

Struggling hard to get below requirement done. Please help. Below is my data

Column A

ABCD PRIVATE LIMITED FED
ABCD PRIVATE LIMITED
PREMIER
ABCD PRIVATE LIMITED LONDEN BRANCH
ABCD INTERNATIONAL FIN
HELLO
ABCD
PQRS

Column B
ABCD
PREMIER

Above being my data in column A and B. I need to search each word in a cell in column A and match to each cell in column B. If any one word matches then i need to print Value of the cell in column A in column C

Below is output required

Column C
ABCD PRIVATE LIMITED FED
ABCD PRIVATE LIMITED
PREMIER
ABCD PRIVATE LIMITED LONDEN BRANCH
ABCD INTERNATIONAL FIN
N/A
ABCD
N/A

**Pete_UK** · 08-24-2012, 06:54 AM

Try this in C1:

=IF(OR(ISNUMBER(SEARCH(B1,A1)),ISNUMBER(SEARCH(B2,A1))),A1,"n/a")

Then copy down.

Hope this helps.

Pete

**raghuprasad.999** · 08-24-2012, 07:01 AM

Not accurate. Below is the result.

Column C

IFIC BANK LIMITED FEDERATION
n/a
PREMIER
IFIC BANK LIMITED MOTIJHEEL BRANCH
IFIC INTERNATIONAL FINANCE
IFIC
ICICI

The formula =IF(OR(ISNUMBER(SEARCH(B1,A1)),ISNUMBER(SEARCH(B2,A1))),A1,"n/a") is searching the word from Column B against Column A. I think i my case; i need to take each word in A1 and search for the word in Column B if any one word matches the i need to print the same in Column C.

Please correct if my explanation is wrong

**Pete_UK** · 08-24-2012, 08:30 AM

You said before:

If any one word matches then i need to print Value of the cell in column A in column C

which is what the formula seems to be doing. Are you now saying that you want the matching word (from column B) to appear in column C ?

Pete

**Jakobshavn** · 08-24-2012, 08:30 AM

Try this small macro:

Sub GatherData()
Dim Na As Long, Nb As Long, L As Long
Dim vB As String, K As Long, J As Long
Na = Cells(Rows.Count, "A").End(xlUp).Row
Nb = Cells(Rows.Count, "B").End(xlUp).Row
For L = 1 To Na
    Cells(L, "C").Clear
Next
For L = 1 To Na
    Cells(L, "C").Value = "N/A"
    ary = Split(Cells(L, "A"), " ")
    For K = 1 To Nb
        vB = Cells(K, "B").Value
        For J = LBound(ary) To UBound(ary)
            If ary(J) = vB Then
                Cells(L, "C").Value = Cells(L, "A").Value
            End If
        Next
    Next
Next
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

**raghuprasad.999** · 08-26-2012, 10:40 PM

Amazing!!! It worked Jakobshavn. Thanks for your favor

**Cutter** · 08-26-2012, 10:54 PM

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.