Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to run a find and replace on multiple values within a range in excel with values from 2 columns: A with the original string; B with the replacement string. I already found a macro that is somewhat working (thanks nixda), but cannot handle the dashes in between some of the longer values in the original string.

Ideally I would like to be able to run it on just the range I selected. An added bonus would be if I could also select the lookup range.

Below is sample values of the original and replacement value columns:

Original values column:

T1-D7-F2-F3-    
T1-D7-F2-F3-C33    
U4 U5 Q2 Q4    
U3-Q7.U1    
R42-43-22-23-

Replacement Values column:

T1,D7,F2,F3    
T1,D7,F2,F3,C33    
U4,U5,Q2,Q4    
U3,Q7,U1    
R42,R43,R22,R23

Update in response to Gary's Student' answer

Values in "data" tab, column A:

1000PF CAP
4 SCREWS
D21 D22 - 709
D4-U3-RV3-Q2-U3
D7 - 00004224
D7 - 0004224
D7 - 004224
D7-F2-C33-F3-U6
D7-F3-J1
END PLATE
END PLATES
F2 F3 - 219
F2-F3-T1
Q11-Q3-J2-J9-
Q2 Q10 U12
Q2 Q3 Q10 Q11
Q2 Q3 Q11 Q10
Q2- Q4- U4- U5
Q2-Q5-Q8-Q10
Q3-Q11.R95
Q5-C10-C35
Q5-C19

Values in "tabl" tab, column A will be same as above

Values in "tabl" tab, column B (replacement values):

1000PF_CAP
4SCREWS
D21,D22,000709
D4,U3,RV3,Q2,U3
D7,004224
D7,004224
D7,004224
D7,F2,C33,F3,U6
D7,F3,J1
END_PLATE(S)
END_PLATE(S)
F2,F3,000219
F2,F3,T1
Q11,Q3,J2,J9
Q2,Q10,U12
Q2,Q3,Q10,Q11
Q2,Q3,Q11,Q10
Q2,Q4,U4,U5
Q2-Q5-Q8-Q10
Q3,Q11,R95
Q5,C10,C35
Q5,C19

After I run the macro, I get the following results, instead of what I expect:

1000PF_CAP CAP
4SCREW(S)S
D21,D22 - 709
D4,U3,RV3-Q2-U3
D700004224
D70004224
D7004224
D7,F2-C33-F3-U6
D7,F3-J1
END_PLATE(S)
END_PLATE(S)S
F2,F3 - 219
F2,F3-T1
Q11-Q3-J2,J9-
Q2,Q10 U12
Q2 Q3 Q10,Q11
Q2,Q3,Q11 Q10
Q2,Q4- U4,U5
Q2-Q5Q8-Q10
Q3,Q11.R95
Q5C10,C35
Q5C19

Any ideas?


Edit

After running this macro to replace values, I have trouble with the following values:

Left column below = Lookup columns A and B have same values (should not change, except for R 3 should become R3).

Right column below = Result after running the macro.

Q?  Q?
Q1  Q?
Q2  Q?
Q3  Q?
Q4  Q?
Q5  Q?
Q6  Q?
Q7  Q?
Q8  Q?
Q9  Q?
QR? QR?
R 3 R?
R?  R?
R0  R?
R1  R?
R2  R?
R3  R?
R4  R?
R5  R?
R6  R?
R7  R?
R8  R?
R9  R?
RE  R?
RV  R?
share|improve this question
    
So you to change the three characters {<space> - .} all to commas?? –  Gary's Student Oct 14 '13 at 21:59
    
Well, not exactly. For example, the original values might be missing the letter R (see last row of example above), and they are corrected (letter R included) in the replacement values. –  Daniel Oct 15 '13 at 3:56
    
The replacement value might be completely different than the original value. Every time the original value is found in the spreadsheet, it should be substituted with its corresponding replacement value from the "lookout" tab. –  Daniel Oct 15 '13 at 4:09
    
@Daniel Please test my fixed code. I made a small mistake with xlPart and xlWhole –  nixda Oct 16 '13 at 19:47
    
@nixda I did test it and it did no replacements? Am I doing something wrong? My tabs are named "data" and "lookup". –  Daniel Oct 16 '13 at 20:49

2 Answers 2

Here is a small sample macro. The translation table is in a tab called "tabl" and the data to be edited is in a tab called "data":

Sub ChangeValues()
    Dim N As Long, L As Long
    Dim rLook As Range
    Sheets("tabl").Select
    N = Cells(Rows.Count, "A").End(xlUp).Row
    aryA = Range("A1:A" & N)
    aryB = Range("B1:B" & N)
    Sheets("data").Select
    Set rLook = Application.InputBox(Prompt:="Select replacement range", Type:=8)
    For L = 1 To N
        rLook.Replace aryA(L, 1), aryB(L, 1)
    Next L
End Sub
share|improve this answer
    
Thanks for the macro! It does what the macro that I had before was doing - some values were replaced ok, some - not. How can I attach a sample spreadsheet here to show what I mean? –  Daniel Oct 15 '13 at 15:54
    
Not possible in this forum –  Gary's Student Oct 15 '13 at 15:56
    
Can I send a sample spreadsheet by e-mail? –  Daniel Oct 15 '13 at 16:38
    
@Daniel Just upload it at ge.tt and edit the link into your question. You mentioned me, can you link me the original question? Maybe I can fix that code –  nixda Oct 16 '13 at 16:47
    
@nixda I saw your solution for a similar problem here: superuser.com/questions/620484/… –  Daniel Oct 16 '13 at 18:36

If you install the RegEx Find/Replace addon, you can use the following regular expressions to accomplish this, and I've tested it out. It works.

Find:           \s*[-.]\s*
Replacement:    ,
Find:           \s+(?!$)
Replacement:    _
share|improve this answer
    
I just noticed that you have a dash at the end of one of the lines, and that ends up indiscriminately replaced as well. You might want to run one more RegEx find/replace. Find ,$ and replace with nothing. That will get rid of any dangling commas like that. –  supercheetah Oct 22 '13 at 20:16
    
Thanks a lot, looks promising! Will give it a try ASAP. The macro that nixda provided works perfectly, I just found one type of entries that cannot be replaced correctly. If I have "Q?" in the lookup range, all "Q1" to "Q9" values get replaced by "Q?". Q11, Q12, etc do not have that problem. Same with "R?". Hope RegEx will be able to take care of that. –  Daniel Oct 31 '13 at 19:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.