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:

Part	Description
03700-38/40	03700-38/40 Volkl Rescue zip RITS maat 38-40
03700-41/43	03700-41/43 Volkl Rescue zip RITS maat 41-43

<colgroup><col><col></colgroup><tbody>
</tbody>

How we want the data: Part Description 03700-38/40 Volkl Rescue zip RITS maat 38-40 03700-41/43 Volkl Rescue zip RITS maat 41-43 <colgroup><col><col></colgroup><tbody> </tbody> Any help, would be much appreciated. Jonathan <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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!