Multiple seach and replace in excel

[_DiLo_]

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

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

All of the codes in column A are unique, and will only appear in colum
B in the same line. So my best description of what I am looking to do i
a line by line, search for the specific code in column A and replac
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 numbe
column A and description column B in my scenario. Thank you in advanc
for taking a look at this. An answer or a point in the right directio
will be amazing.

PART NUMBER - DESCRIPTIO
CE271A - HP Color LaserJet CE271A Cyan Print Cartridg
CE272A - HP Color LaserJet CE272A Yellow Print Cartridg
CE273A - HP Color LaserJet CE273A Magenta Print Cartridg
CE278A - HP LaserJet P1566/P1606 Black Print Crtg HP Standar

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

 

[Mazzaropi]

lets see if I can get this right, i only have one option for zippin
files. But if it inst correct, the information above is exactly as i
would appear in columns A and B respectively. A quick insert into exce
should work.

<<<<< *HELP from BRAZIL* >>>>>

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 da

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

 

[_DiLo_]

<<<<< *HELP from BRAZIL* >>>>>

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

Fantastic, fantastic, fantastic. Thank you very much

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

 

[Ron Rosenfeld]

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


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

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
======================================