Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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 hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#5
|
|||
|
|||
Quote:
Last edited by _DiLo_ : May 9th 12 at 07:36 PM |
#6
|
|||
|
|||
Quote:
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
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#7
|
|||
|
|||
Fantastic, fantastic, fantastic. Thank you very much.
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ====================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel seach | Excel Worksheet Functions | |||
Seach & Replace Macro | Excel Programming | |||
seach and replace '(' with an '/' | Excel Discussion (Misc queries) | |||
Excel Forumla to seach and return all values | Excel Worksheet Functions | |||
Seach Column and return multiple dates to another worksheet? | Excel Worksheet Functions |