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

Dear _DiLo_, Good Morning.

Any chance you could show us an example of the workbook to make it easier to understand the issue?

The forum only accept .zip files

Attach your workbook here and put there an example of your waited results.

I believe that your question will be solved quickly.

I have attached an example. I highlighted 2 of the examples, column A. that is the data that needs to be deleted from the column B of that line. (red text) Any ideas?

ATTENTION

You attached a shortcut of your workbook to YOUR computer NOT a file.

To attach a .zip file he

a) ZIP your workbook xls or xlsx etc
b) Use a button Manage Attachments at the bottom part of screen
c) type a name of it at "Upload File from your Computer" field or use the find file option.
d) click the upload button.
It´s done.

lets see if I can get this right, i only have one option for zipping files. But if it inst correct, the information above is exactly as it would appear in columns A and B respectively. A quick insert into excel 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 day

Fantastic, fantastic, fantastic. Thank you very much.

Multiple seach and replace in excel
 

On Wed, 9 May 2012 15:07:41 +0000, _DiLo_ wrote:


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