Excel - Multiple seach and replace in excel

I have been battling excel for a while now. If anyone has an answer to
this I would greatly appreciate the help.

What I am looking to do is a complicated, at least for me, search and
replace. I am looking to, row by row , find a 6-12 digit code located in
column A and replace that information from a text in the corresponding
column B of that line with an *. I need this process to repeat over
thousands of lines. It can not alter the original code in column A. And
as a kicker some of the codes that are in column A may or may not appear
in the corresponding column B.

All of the codes in column A are unique, and will only appear in column
B in the same line. So my best description of what I am looking to do is
a line by line, search for the specific code in column A and replace
with an * in column B.

A small sample of the spreadsheet I am trying to manipulate is below.
For some reason I could not upload an excel doc.?? Consider part number
column A and description column B in my scenario. Thank you in advance
for taking a look at this. An answer or a point in the right direction
will be amazing.

PART NUMBER -	DESCRIPTION
CE271A -	HP Color LaserJet CE271A Cyan Print Cartridge
CE272A -	HP Color LaserJet CE272A Yellow Print Cartridge
CE273A -	HP Color LaserJet CE273A Magenta Print Cartridge
CE278A -	HP LaserJet P1566/P1606 Black Print Crtg HP Standard


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
_DiLo_

_DiLo_;1601671 Wrote:


Dear *_Dilo_*, Good Afternoon.

I did an example for you.
Take a look at it and tell me if it worked for you.

Fell free to ask anything about your question.

Have a nice day


+-------------------------------------------------------------------+
|Filename: problem_workbook_SOLVED.zip                              |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=377|
+-------------------------------------------------------------------+



--
Mazzaropi

Mazzaropi;1601677 Wrote:

Fantastic, fantastic, fantastic. Thank you very much.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
_DiLo_

I understand what you want to do is
If the part number in column A is included in the Description in Column B in the same row then
Replace that part number in Column B with an asterisk "*"
Otherwise leave the description alone.

You can do this with a worksheet formula, if you can accept not changing the information in col B also.  For example, with your data in columns A & B

C2:  =IFERROR(REPLACE(B2,FIND(A2,B2),LEN(A2),"*"),B2)
and fill down as far as required.

If you really want to change the information in Col B, you could either copy the results in Col c, the Paste Special Values over Col B; or you could use a macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

==============================================
Option Explicit
Sub ReplacePartNum()
Dim rSrc As Range, c As Range
Dim rw As Range
Set rSrc = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)
Application.ScreenUpdating = False
For Each rw In rSrc.Rows
rw.Cells(2) = Replace(rw.Cells(2), rw.Cells(1), "*")
Next rw
Application.ScreenUpdating = True
End Sub
======================================

Send a file to dguillett1@gmail.com with a complete explanation.