Removing value in column A from sub-string in column B

[Jonathan Jones]

Hi,

I have a list of product codes in column A, and a list of product descriptions in column B. On many rows the product descriptions contains the product code. On rows where the product decription contains the product code, I would like to remove the product code. Example below:

Data is currently:

[TABLE="width: 926"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]03700-38/40[/TD]
[TD]03700-38/40 Volkl Rescue zip RITS maat 38-40[/TD]
[/TR]
[TR]
[TD]03700-41/43[/TD]
[TD]03700-41/43 Volkl Rescue zip RITS maat 41-43[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 926"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]
How we want the data:

[TABLE="width: 926"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 926"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]03700-38/40[/TD]
[TD]Volkl Rescue zip RITS maat 38-40[/TD]
[/TR]
[TR]
[TD]03700-41/43[/TD]
[TD]Volkl Rescue zip RITS maat 41-43[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Any help, would be much appreciated.

Jonathan[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Heres a formula that does but it obviously needs to go in another cell. If you need to overwrite the description then you would need code.

=TRIM(SUBSTITUTE(B2,A2,""))

 

[BarryL]

As steve mentioned, if you need code try the below

Sub test()
Dim addrA As String, addrB As String
 addrA = "A2:A" & Range("A" & Rows.Count).End(xlUp).Row


 addrB = "B2:B" & Range("B" & Rows.Count).End(xlUp).Row


  Range(addrB).Value = Evaluate(Replace("IF(ISNUMBER(SEARCH(" & addrA & ",@)),TRIM(SUBSTITUTE(@," & addrA & ","""")),@)", "@", addrB))
End Sub

 

[Jonathan Jones]

As steve mentioned, if you need code try the below

Sub test()
Dim addrA As String, addrB As String
 addrA = "A2:A" & Range("A" & Rows.Count).End(xlUp).Row


 addrB = "B2:B" & Range("B" & Rows.Count).End(xlUp).Row


  Range(addrB).Value = Evaluate(Replace("IF(ISNUMBER(SEARCH(" & addrA & ",@)),TRIM(SUBSTITUTE(@," & addrA & ","""")),@)", "@", addrB))
End Sub

Brilliant - thank you!