Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to find and replace multiple values at once in Excel?

doc multiple find replace 1

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

arrow blue right bubble 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:

doc multiple find replace 2

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 Extendoffice
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

4. 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.

doc multiple find replace 3

5. Click OK, and another prompt box is displayed to remind you select the criteria which you are created in step 1. See screenshot:

doc multiple find replace 4

6. Then click OK, all the specific values have been replaced with the new values as you need immediately.

doc multiple find replace 5

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, charts and 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-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom

 

Comments (132)
No ratings yet. Be the first to rate!
Ken Long
Ken Long
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
Ken Long
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
Cory     Ken Long
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
Markus
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 Wide Replacement values: 1/32 0,794 mm 1/16 1,588 mm . . . 11/16 17,463 mm . . . 1/1 25,400 mm Result Line: Beads Measure Approximately: 11,588 mm Inch Wide is wrong Problem: Code uses Value from 1/16 an replaces it How can I fix this ? (If I use LookAt:=xlWhole in another Macro I dont get any result)
Reply
Mohajel
Mohajel
This comment was minimized by the moderator on the site
Hi Thank you Can I make it case sensitive?
Reply
bany
bany
This comment was minimized by the moderator on the site
OMG, I witness Jesus here!!
Reply
Jake
Jake
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
Tom     Jake
This comment was minimized by the moderator on the site
You need to add the following parameter: MatchCase:=True So for instance, add it to the below line: InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, MatchCase:=True This will find and replace only items that MatchCase in the Range you have selected.
Reply
trisha
trisha     Tom
This comment was minimized by the moderator on the site
Can you rewrite code to make sure we got it right?
Reply
trisha
trisha     Tom
This comment was minimized by the moderator on the site
It didn't work for me, help rewrite code, please
Reply
JD
JD
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
Kartik
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
jegadeez
This comment was minimized by the moderator on the site
ORIGINAL FORMAT: 1 in ID X 2-1/2 in OD X 40 in Length 1 in ID X 2-1/2 in OD X 10 in Length 1 in ID X 2-1/2 in OD X 20 in Length 1 in ID X 2-1/2 in OD X 10 in Length 9-1/2 in ID X 14 in OD X 12 in Length REQUIRED FORMAT: ID 1 x OD 2-1/2 x LG 40 IN ID 1 x OD 2-1/2 x LG 10 IN ID 1 x OD 2-1/2 x LG 20 IN ID 1 x OD 2-1/2 x LG 10 IN ID 9-1/2 x OD 14 x LG 12 IN PLEASE TELL ME FORM THE GIVEN EXAMPLE, HOW TO REPLACE THE ORIGINAL FORMAT TO REQUIRED FORMAT IN EXCEL
Reply
Khan
Khan
This comment was minimized by the moderator on the site
Thank you it helped me
Reply
Khan
Khan
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
Ram     Khan
This comment was minimized by the moderator on the site
At first replace ABC 2 with TTT and then ABC with RRR
Reply
Xhaleira
Xhaleira
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
Swetha
This comment was minimized by the moderator on the site
Wow!!! Thanks a lot! Worked like a gem.
Reply
KiranRamannaIyer
KiranRamannaIyer
This comment was minimized by the moderator on the site
Works like charm! Thanks a lot :D (Y)
Reply
Dushyanth
Dushyanth
This comment was minimized by the moderator on the site
I used the above code to replace numbers but both 11 and 1 are being replaced.
Reply
mmg1
mmg1     Dushyanth
This comment was minimized by the moderator on the site
Sub MultiFindNReplace() 'Update 20140722 Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, lookat:=xlwhole Next Application.ScreenUpdating = True End Sub
Reply
Kenneth
Kenneth     mmg1
This comment was minimized by the moderator on the site
Yes!! Thank you for this code, it worked like a charm
Reply
Excelsucks
Excelsucks     Kenneth
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
Jairam
This comment was minimized by the moderator on the site
Thank u. Its working great.
Reply
ScottR
ScottR
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
SUNIL SINGHAL
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
Sharv
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
Sharv
This comment was minimized by the moderator on the site
I have 3 columns as below, 1 Multimeter 2 Test Lamp 3 Ohm Meter 4 Resistance Tester 5 Fault Loop 12 RCD Tester 24 Portable RCD 13 Extension Lead Third 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
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations