Subtitute contents from one cell into another

[davidmg1982]

Morning everyone, i will appreciate your help to elaborate a Subroutine or UDF to substitute/delete all the words from cell B1 into cell A1, showing the result in cell C1, example:
B1 may have more than one word, all separated by comma + space ", "
[TABLE="width: 836"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]ALTEA NOTIFICATION HDQQFLR006 FILESYSTEM VOL XLQCHANNELAPI34 OVER[/TD]
[TD]HDQQFLR006, XLQCHANNELAPI34[/TD]
[TD]ALTEA NOTIFICATION FILESYSTEM VOL OVER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance for your guidance!

 

[mumps]

This macro should work for as many rows of data that you have.

Sub davidmg1982()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    Dim i As Long
    For Each rng In Range("B1:B" & LastRow)
        splitRng = Split(rng, ", ")
        For i = LBound(splitRng) To UBound(splitRng)
            rng.Offset(0, -1) = Replace(rng.Offset(0, -1), splitRng(i), "")
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

This macro should work for as many rows of data that you have.

Sub davidmg1982()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    Dim i As Long
    For Each rng In Range("B1:B" & LastRow)
        splitRng = Split(rng, ", ")
        For i = LBound(splitRng) To UBound(splitRng)
            rng.Offset(0, [B][COLOR="#FF0000"][SIZE=4]-1[/SIZE][/COLOR][/B]) = Replace(rng.Offset(0, -1), splitRng(i), "")
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

Shouldn't the red highlighted -1 actually be +1 ?

 

[mumps]

Thank you Rick. You are absolutely correct.

 

[davidmg1982]

This macro should work for as many rows of data that you have.

Sub davidmg1982()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    Dim i As Long
    For Each rng In Range("B1:B" & LastRow)
        splitRng = Split(rng, ", ")
        For i = LBound(splitRng) To UBound(splitRng)
            rng.Offset(0, -1) = Replace(rng.Offset(0, -1), splitRng(i), "")
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

AMAZING, thank you so much for sharing your knowledge.

 

[mumps]

You are very welcome. And thanks to Rick for picking up on my error.

 

[davidmg1982]

You are very welcome. And thanks to Rick for picking up on my error.

It was a happy error, the big issue was solved, thanks mumps and Rick!