Finding Common word from two columns and returning value

Question

I've two collumns in excel and I want to see if there is a common word(s) betweeen my cells, for instance:

Cell A1: 76A08, 26A65, 22A26, 2A01, 12A05

Cell B1 I've : 26A65, 22A26

In cell C1 the result exepted is : 26A65, 224A26

I have over 7000 rows.

Answer 1

This VBa will do it

Rememeber to take a back up of your file first in case! (There is no undo option in VBa)

Since you have blank lines, but know how many rows, you can use this UNTESTED code.

Option Explicit
Sub doTheThing()

Dim row As Integer
row = 1

Dim totalRows as Integer
totalRows = 7000                      'Change the number to the total rows

for row = 1 to totalRows 

    Dim splitty() As String
    splitty = Split(Range("B" & row), ",")
    Dim i As Integer

    For i = 0 To UBound(splitty)
        Dim sp As String
        sp = splitty(i)
        If InStr(Range("A" & row).Value, Trim(sp)) Then
            Range("C" & row).Value = Range("C" & row) & sp & " "
        End If

    Next i

Next row

End Sub

How do I add VBA in MS Office?

Before

After