CODEWITHSUNDEEP
vbaexcel
user9317722
user9317722

Reputation:

Seeing if text in cell contains same text in different cell

i have a table in Excel and i would like to create a macro where it checks if the text in all the cells Column B and C are present in the cells in Column A, and then remove the matching words from Column A. Example: The macro will see that A1 has red and blue in its text, so does B1 and C1. It will then remove the text in A1 that matches with B1 C1. The macro will then repeat for A2, B2, C2, and A3, B3, C3.

When i tried that with my code, it would just erase everything 

       A           B      C 
1  redbluegreen | blue | red
2  greypinkblack| pink | grey
3  yellowpurple |purple| yellow

Here's my code:

Sub Sort()

Dim A As String
Dim B As String
Dim C As String
Dim Result As String

Columns(1).Value = A
Columns(2).Value = B
Columns(3).Value = C

If A.contains(B) Or A.contains(C) Then
    Result = InStr(1, B ,"")
    A = Result
End If

End Sub

Upvotes: 0

Views: 125

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Sub Sort()
    Dim cell As Range

    For Each cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        cell.Value= Replace(Replace(cell.Text, cell.Offset(,1).Text, "") , cell.Offset(,2).Text, "")
    Next
End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

If I understand, you don't need VBA. SUBSTITUTE() does the trick:

=SUBSTITUTE(SUBSTITUTE($A2,C2,""),B2,"")

enter image description here

Upvotes: 3

Related Questions