Excel 2007 : Find conents from one cell and delete from another cell

**MichaelSudik** · 06-10-2012, 06:35 PM

Hello everyone,

I hope someone can help me.

I just want to find the value in one cell, search and find that value in another cell and delete that value from the text in that cell.

Example:

Cell A Contains:
884922

Cell D Contains
ISO Certified 884922 Black Copier Cartridge compatible with the Gestetner DSM 735E,745E/ Lanier LD-040,LD-050,LD-335,LD-345/ Ricoh MP3500,MP4000,MP4500,MP5000 Series/ Savin 8035E,8045E,9040,9050. The use of compatible supplies does not void your printer warranty. Gestetner DSm735, Gestetner DSm745, Lanier LD040, Lanier LD050, Lanier LD335, Lanier LD345, Ricoh Aficio MP 4000, Ricoh Aficio MP 4500, Ricoh Aficio MP 4500, Ricoh Aficio MP 5000, Savin 8035e, Savin 8045e, Savin 9040, Savin 9050,

I want to delete 884922 from the text string in cell D.

Any help is GREATLY appreciated!!

**npamcpp** · 06-10-2012, 06:47 PM

Hello, you cannot use a formula to change the value of another cell. That would require VBA. But you can use a formula to show the result in a different cell.

For example, in cell E1 use this formula

=SUBSTITUTE(D1,A1,"")

The result is the text with the number removed. You can now copy the result and use Paste Special > Values to replace the original text.

**MichaelSudik** · 06-10-2012, 07:08 PM

Excellent!! Got it. Thank you very much for your help!!

**Skidder** · 06-10-2012, 11:20 PM

What is wrong with using Ctrl F to find and replace...at least it modifies the live text without having to recopy and paste.......

**npamcpp** · 06-11-2012, 12:42 AM

Skidder, I assume that the original file has hundreds of rows and the value to be removed from each row differs. That value lives in column A. Using Find/Replace will make sense, unless there are hundreds of different values to be replaced. In that case, a formula approach would be faster.

**MichaelSudik** · 06-11-2012, 01:00 AM

Originally Posted by npamcpp

Skidder, I assume that the original file has hundreds of rows and the value to be removed from each row differs. That value lives in column A. Using Find/Replace will make sense, unless there are hundreds of different values to be replaced. In that case, a formula approach would be faster.

That is correct....different value for each cell.....too many different values for CTL F