Search and replace part of cell based on another?

[PeterSkotte]

I need some help with a (for me) tricky search and replace(remove)

Here is an example:

I have some rows containing (just as an example):

Column A

Row1: top category/subcategory1./subcategory2-1/subcategory3 2/filename 123 bb

Row2: top category/subcategory1-/subcategory2-2/filename 23323 x - 87h

Row3: top category/subcategory1./subcategory2-1/subcategory3 2/subcategory4 1/filename 123 bb+kkjas

I need to remove the last part of each cell based on another cell.

Fortunately I have the text I need to remove singled out in another column. Example:

Column B

Row1: filename 123 bb

Row2: filename 23323 x - 87h

Row3: filename 123 bb+kkjas

But I am lost on how I can remove the exact text from the rows in column A based on the content in column B?

So that the result becomes:

Column C

Row1: top category/subcategory1./subcategory2-1/subcategory3 2/

Row2: top category/subcategory1-/subcategory2-2/

Row3: top category/subcategory1./subcategory2-1/subcategory3 2/subcategory4 1/

Simplyfied a lot I would like to achieve : C1=A1-(contentof)B1

[oweng]

Do you need to search (selectively) based on cell values, or are you wanting to simply remove the filename part from the end of each full path?

[mariosv]

I think you can use something like:

C1: =IFERROR(LEFT(A1;SEARCH(B1;A1)-2);A1)

if you are sure the B1 is always part of A1, simplify to:

C1: =LEFT(A1;SEARCH(B1;A1)-2)

works as well.

[oweng]

@mariosv, rather than adjust the search position, you can use a basic regular expression to find the last forward slash e.g., =LEFT(A1,SEARCH("/[^/]*$";A1;1)). This finds the first instance of a slash / followed by zero or more non-slash characters [^/]* prior to the end of line $.

[PeterSkotte]

Super Nice. Works like a charm. Thank you so much for helping out.