As we all known, we can use Find and Replace function to find multiple same cell and replace them with a value as you need. But sometimes, you need to apply many-to-many replacement simultaneously. For example, I have a range of data, and now I want to replace all Apples to Red Apples, Oranges to Green Oranges, Bananas to Yellow Bananas and so on as following screenshots shown, do you have any good ideas to solve this task in Excel?
Find and replace multiple values at once with VBA code
Find and replace multiple values at once with VBA code
If you are tired of find and replace the values time and time again, the following VBA code can help you to replace multiple values with your needed texts at once.
1. Please create your conditions that you want to use which contain the original values and new values. See screenshot:
2. Then hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module window.
VBA code: Find and replace multiple values at once
Sub MultiFindNReplace()'Updateby ExtendofficeDim Rng As RangeDim InputRng As Range, ReplaceRng As RangexTitleId = "KutoolsforExcel"Set InputRng = Application.SelectionSet InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)Application.ScreenUpdating = FalseFor Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).ValueNextApplication.ScreenUpdating = TrueEnd Sub4. Then press F5 key to run this code, in the popped out prompt box, please specify the data range that you want to be replaced the values with new values.
5. Click OK, and another prompt box is displayed to remind you select the criteria which you are created in step 1. See screenshot:
6. Then click OK, all the specific values have been replaced with the new values as you need immediately.
Related articles:
How to find and replace specific text in text boxes?
How to find and replace text in chart titles in Excel?
How to find and replace text within comments in Excel?
How to change multiple hyperlink paths at once in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More... Free Download... Purchase...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Read More... Free Download... Purchase...
Sort comments by
Oldest First
Newest First
Comments (136)
No ratings yet. Be the first to rate!
Ken Long
#12824
This comment was minimized by the moderator on the site
This didn't work for me. I'm using Excel 2010. It prompted me for the original range and the new range then failed with no messages. Nothing was replaced.I will continue to search for a way to do this.
Reply
Ken Long
#12825
This comment was minimized by the moderator on the site
Please disregard my last comment. I was doing it wrong. It worked fine!
Reply
Cory Ken Long
#12826
This comment was minimized by the moderator on the site
[quote]Please disregard my last comment. I was doing it wrong. It worked fine!By Ken Long[/quote]It does not work for me.How were you doing it wrong.I have zero knowledge of VBA.
Reply
Markus
#13084
This comment was minimized by the moderator on the site
Hi,I tried to use this macro to translate measurements.Line to be translated: Beads Measure Approximately: 11/16 Inch WideReplacement values:1/320,794 mm1/161,588 mm...11/1617,463 mm...1/125,400 mmResult Line:Beads Measure Approximately: 11,588 mm Inch Wide is wrongProblem:Code uses Value from 1/16 an replaces itHow can I fix this ?(If I use LookAt:=xlWhole in another Macro I dont get any result)
Reply
Mohajel
#13211
This comment was minimized by the moderator on the site
HiThank you Can I make it case sensitive?
Reply
bany
#13280
This comment was minimized by the moderator on the site
OMG, I witness Jesus here!!
Reply
Jake
#13296
This comment was minimized by the moderator on the site
Hi,I've tried to utilize this code with replacing multiple numbers with corresponding text.I have a large data set ranging from 1 -> 112000, but when I try to convert 1 to its corresponding string value (eg. Apple), it will translate this towards every single numerical 1 in the column.Therefore 112000 will be outputted as AppleAppleOrange000.Is there a way in this Kutools process, like the find/replace tool in excel, to find/replace specific match cases?
Reply
Tom Jake
#13297
This comment was minimized by the moderator on the site
You need to add the following parameter:MatchCase:=TrueSo for instance, add it to the below line:InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, MatchCase:=TrueThis will find and replace only items that MatchCase in the Range you have selected.
Reply
trisha Tom
#13298
This comment was minimized by the moderator on the site
Can you rewrite code to make sure we got it right?
Reply
trisha Tom
#13299
This comment was minimized by the moderator on the site
It didn't work for me, help rewrite code, please
Reply
JD
#13333
This comment was minimized by the moderator on the site
is there a way to default the Original range to 'Tab1'!$4:$42
Reply
Kartik
#13435
This comment was minimized by the moderator on the site
Worked like a charm!Thanks for the code and detailed instructions... You have saved my two days...God Bless!!
Reply
jegadeez
#13478
This comment was minimized by the moderator on the site
ORIGINAL FORMAT:1 in ID X 2-1/2 in OD X 40 in Length1 in ID X 2-1/2 in OD X 10 in Length1 in ID X 2-1/2 in OD X 20 in Length1 in ID X 2-1/2 in OD X 10 in Length9-1/2 in ID X 14 in OD X 12 in LengthREQUIRED FORMAT:ID 1 x OD 2-1/2 x LG 40 INID 1 x OD 2-1/2 x LG 10 INID 1 x OD 2-1/2 x LG 20 INID 1 x OD 2-1/2 x LG 10 INID 9-1/2 x OD 14 x LG 12 INPLEASE TELL ME FORM THE GIVEN EXAMPLE, HOW TO REPLACE THE ORIGINAL FORMAT TO REQUIRED FORMAT IN EXCEL
Reply
Khan
#13571
This comment was minimized by the moderator on the site
Thank you it helped me
Reply
Khan
#13574
This comment was minimized by the moderator on the site
The Macro does not work for exact match. Like there are two categories ABC and ABC 2. Find and replace ABC with RRR and ABC 2 with TTT so Macro will change ABC 2 to RRR 2 instead of TTT.
Reply
Ram Khan
#13575
This comment was minimized by the moderator on the site
At first replace ABC 2 with TTT and then ABC with RRR
Reply
Xhaleira
#13615
This comment was minimized by the moderator on the site
Under "Original range" you show $A$2:$A$10, which suggest it is within a single sheet. How can I select the whole workbook here?
Reply
Swetha
#13646
This comment was minimized by the moderator on the site
Wow!!! Thanks a lot! Worked like a gem.
Reply
KiranRamannaIyer
#13682
This comment was minimized by the moderator on the site
Works like charm! Thanks a lot :D (Y)
Reply
Dushyanth
#13777
This comment was minimized by the moderator on the site
I used the above code to replace numbersbut both 11 and 1 are being replaced.
Reply
mmg1 Dushyanth
#13778
This comment was minimized by the moderator on the site
Sub MultiFindNReplace()'Update 20140722Dim Rng As RangeDim InputRng As Range, ReplaceRng As RangexTitleId = "KutoolsforExcel"Set InputRng = Application.SelectionSet InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)Application.ScreenUpdating = FalseFor Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, lookat:=xlwholeNextApplication.ScreenUpdating = TrueEnd Sub
Reply
Kenneth mmg1
#13779
This comment was minimized by the moderator on the site
Yes!! Thank you for this code, it worked like a charm
Reply
Excelsucks Kenneth
#13780
This comment was minimized by the moderator on the site
This doen't work either when trying to replace a column of numeric values with other numeric values from pivot table. This is what i get for trying to do simple to automate stuff in Excel spreadshit.
Reply
Jairam
#13784
This comment was minimized by the moderator on the site
Thank u. Its working great.
Reply
ScottR
#13825
This comment was minimized by the moderator on the site
I have the same issue where if 2 names are similar, such as if I wanted to replace TEACHER with 1 and TEACHER ASSIST with 2, it would replace TEACHER with 1 and TEACHER ASSIST with 1 ASSIST. How would I get it to replace the EXACT phrase?
Reply
SUNIL SINGHAL
#13874
This comment was minimized by the moderator on the site
Thanks a lot. It was quite useful and saved me 1 hour of labor
Reply
Sharv
#14010
This comment was minimized by the moderator on the site
I have two columns, 1st column is list on number 1,2,3.... 22,23,... Second column with list of strings.Instead of replacing the value of 12 with string value. It replaces it with strings from 1 and 2. Anyone knows how to sort this?
Reply
Sharv
#14011
This comment was minimized by the moderator on the site
I have 3 columns as below,1Multimeter2Test Lamp3Ohm Meter4Resistance Tester5Fault Loop 12RCD Tester24Portable RCD13Extension LeadThird columns has number 1,2...50. There are about 36000 rows. Its replacing 12 with values from the row 1 and 2 instead of 12. Anyonw know how to fix it?
Reply
There are no comments posted here yet
Load More