Ask Your Question
0

Search and replace part of cell based on another? [closed]

asked 2014-02-05 23:24:21 +0200

PeterSkotte gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 07:21:41.988002

Comments

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?

oweng gravatar imageoweng ( 2014-02-06 01:20:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2014-02-06 00:48:31 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

@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 $.

oweng gravatar imageoweng ( 2014-02-06 01:17:50 +0200 )edit

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

PeterSkotte gravatar imagePeterSkotte ( 2014-02-06 18:35:17 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-02-05 23:24:21 +0200

Seen: 775 times

Last updated: Feb 06 '14