Find a set of string and then remove it if found.

I have pasted the image for the query in which i want that,

If the data in the column “A” contains the any of strings available in the column “B”, then the matching string (maximum part of it) should be removed and the resultant part should be left in column “D”.

Excel Query Image

 

List of input URLs in text :

 

https://example.com/shop/g/IT_CAN_BE_ANY_TEXT
https://example.com/shop/h/IT_CAN_BE_ANY_TEXT
https://website.com/IT_CAN_BE_ANY_TEXT
https://example.com/IT_CAN_BE_ANY_TEXT
https://website.com/offer/IT_CAN_BE_ANY_TEXT
https://website.com/events/IT_CAN_BE_ANY_TEXT
https://example.com/IT_CAN_BE_ANY_TEXT
https://example.com/shop/p/IT_CAN_BE_ANY_TEXT
https://website.com/shop/p/IT_CAN_BE_ANY_TEXT

List of strings to be removed:

 

https://example.com/
https://website.com/
https://website.com/offer/
https://example.com/shop/g/
https://website.com/shop/g/
https://example.com/shop/h/
https://website.com/shop/h/
https://website.com/events/
https://example.com/shop/p/
https://website.com/shop/p/

Result Needed for each URL:

IT_CAN_BE_ANY_TEXT

 

 

By: Prasun

Leave a Reply

Your email address will not be published. Required fields are marked *