Excel Filter: Search the contents of one cell for the contents of another cell, then replace with the contents of yet another cell? Is it possible?

February 24, 2010 3:42 PM

I need to search the text in column A for the text in column B, replace it with the text in column C, and return the new complete string to column D. I've Googled. I've read. I've cried. Can you please help me?

I know I'm going to kick myself for not knowing this (and for failing at the Googles)...

The sheet is set up as shown below. I need to parse A for B and replace the found string with C, and return the end result to a new column, D. Then I need to apply it to the other 5000 records in the sheet.

----Column A----------Column B----------Column C------Column D
Foo Foo2 Foo3-----------Foo2--------------Foo4--------Foo Foo4 Foo3

I'm really hoping that this is endlessly complicated, so as to save me some personal embarrassment.

Thanks very much for taking the time to look!

posted by Salient to Computers & Internet (5 answers total)

Best answer: I think you want the SUBSTITUTE function
posted by ish__ at 3:55 PM on February 24, 2010

Best answer: Beaten on preview!

ish has it: in D1 you want: =SUBSTITUTE(A1,B1,C1)
posted by hjd at 3:58 PM on February 24, 2010

Response by poster: Worked perfectly. Thank you, sir!
posted by Salient at 3:59 PM on February 24, 2010

I also think it sucks that the REPLACE function doesn't do this. I can't tell you how many times I've googled for for the same damn thing and only come up with "how to use the find and replace dialog"

So now I have a sticky note on my desk about it for quick reference :)
posted by ish__ at 4:13 PM on February 24, 2010

Response by poster: I now have an identical sticky. Thanks again.
posted by Salient at 4:21 PM on February 24, 2010