Skip to main content

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?

 

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead 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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 

 

Comments (136)
No ratings yet. Be the first to rate!
Chuaser
Chuaser
This comment was minimized by the moderator on the site
Sub MultiFindNReplace()
'Actualizar 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
0
0
John Pico
John Pico
This comment was minimized by the moderator on the site
amigo este post tiene como 8 años pero funciona, que buen aporte para los que no sabemos excel
Reply
0
0
Bruno
Bruno
This comment was minimized by the moderator on the site
Dei uma modificada no código, e no meu caso, as referências de células são estáticas:

Sub MetodoReplace()

Dim rng As Range, xTitleId As String
Dim inputRng As Range, replaceRng As Range

Application.ScreenUpdating = False

xTitleId = "Metodo Replace"
Set inputRng = Range("A1").CurrentRegion.Offset(1, 0)
Set inputRng = Application.InputBox("Original range:", xTitleId, inputRng.Range("A1").CurrentRegion.Address, _
Type:=8)
Set replaceRng = Application.InputBox("Reaplicar Range:", xTitleId, inputRng.Range("C2").CurrentRegion.Address, _
Type:=8)

For Each rng In replaceRng.Columns(1).Cells
inputRng.Replace What:=rng.Value, Replacement:=rng.Offset(0, 1).Value
Next

Application.ScreenUpdating = True
Reply
0
0
Иван
Иван
This comment was minimized by the moderator on the site
Благодарю за экономию моего времени! Успехов!
Reply
0
0
Alex
Alex
This comment was minimized by the moderator on the site
Hallo,

ich würde gerne den Text in den Tabellen des Jahresabschlusses automatisch von Deutsch ins Englische übersetzen.
Dazu habe ich eine Excel Arbeitsmappe in welchem die Übersetzungstabelle "Ersatz" enthalten ist und in den folgenden Tabellenblättern die verschiedenen Tabellen wie zb. die Konzernbilanz, GuV, Geldflussrechnung etc.
Dazu habe ich ihren Code versucht zu transformieren. Die Übersetzung erfolgt im Grunde auch, aber zb. Vermögen - Assets; Finanzielle Vermögenswerte - Financial Assets wird nicht korrekt übersetzt, die Finanziellen Vermögenswerte übersetzt es als "Financial Assetswerte". auch in thousands EUR wir offensichtlich mit USA kombiniert.

Hier der Code (meine VBA Kenntnisse sind nur sehr minimalistisch):
Sub MultiFindNReplace2()
'Updateby Alex
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
Dim Ws As Worksheet

Set Ws = Worksheets("Konzernbilanz")
Set Ws = Worksheets("Konzerngesamtergebnis")
Set Ws = Worksheets("Konzerneigenkapitalspiegel")
Set Ws = Worksheets("Konzerngeldfluss")
Set InputRng = Ws.Range("D:Z")

Set Ws = Worksheets("Ersatz")
Set ReplaceRng = Ws.Range("A:B")

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

Evtl. haben sie auch eine Idee wie ich dem Programm sagen kann, dass alle Tabellenblätter übersetzt werden müssen, außer natürlich die Tabelle "Ersatz".

Vielen Dank

Sg
Alex
Reply
0
0
Roman
Roman
This comment was minimized by the moderator on the site
Buenas !

Sabe alguien si se puede ejecutar en varias hojas de un mismo excel simultaneamente ?


Muchas gracias !!!
Reply
0
0
skyyang
skyyang     Roman
This comment was minimized by the moderator on the site
Hello, Roman,
If you want to find and replace in multiple worksheets, the following VBA code may do you a favor.
Note: Sheet1, Sheet2 are the sheet names that you want to find and replace values from, please separate the sheet names by comma; A1:AA100is the original data range you want to replace the specific values.
Sub MultiFindNReplace()
'Updateby Extendoffice
Dim xArrSh()
Dim Rng As Range
Dim xStrRgAddress As String
Dim InputRng As Range, ReplaceRng As Range
Dim xCells As Range
Dim xWSHs As Sheets
Dim xWSh As Worksheet
xArrSh = Array("Sheet1", "Sheet2") 'The sheet names, please separate the sheet names by comma
xStrRgAddress = "A1:AA100" 'the original data range you want to replace
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
If ReplaceRng Is Nothing Then Exit Sub
Set xWSHs = Application.ActiveWorkbook.Worksheets
Application.ScreenUpdating = False
For xI = 0 To UBound(xArrSh)
Set xWSh = xWSHs(xArrSh(xI))
Set xCells = xWSh.Range(xStrRgAddress)
For Each Rng In ReplaceRng.Columns(1).Cells
    xCells.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Next
Application.ScreenUpdating = True
End Sub


After inserting the code, run this code, and a prompt box will pop up, select the original data and new data you want to find and replace with. see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-multiple-find-replace.png

Please try, hope it can help you!
Reply
Report
0
0
Rosemar
Rosemar
This comment was minimized by the moderator on the site
Olá amigos o código é interessante porem não consegui que funciona-se ficarei muito grato se alguém se dispor a ajuda tenho certeza que será útil para outras pessoas.
exemplo:
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 20 21 22 23 25
tenho esses números em uma linha
2 4 5 9 10 11 12 14 15 17 18 20 21 22 25
2 3 4 5 6 8 9 10 11 16 17 18 20 22 23
2 3 4 5 6 7 8 10 11 12 14 15 16 21 22
2 3 4 7 8 10 11 13 14 15 17 20 22 23 25
2 3 8 9 10 11 12 13 14 16 18 20 21 23 25
2 5 6 7 8 9 11 12 13 15 17 18 21 22 23
3 4 6 7 9 11 12 14 15 16 17 18 20 23 25
2 3 4 6 7 8 9 13 14 16 17 18 21 22 25
3 5 6 10 13 14 15 16 17 18 20 21 22 23 25
2 4 5 6 7 8 9 10 12 13 15 16 17 20 25
e esse grupo em outras porem preciso localizar e substituir e criar vinculo das células com seus respectivos números
exemplo: 02 com 02, 03 com 03, 04 com 04
att: Rosemar
Reply
Report
0
0
MandyZhou
MandyZhou     Rosemar
This comment was minimized by the moderator on the site
Hello friend,

Sorry to hear that. Your situation is different from the example in the article. Our VBA code can only replace the original values with the value you define. It can't create a link between the cells with their respective numbers. Sorry for that. Have a nice day.

Sincerely,
Mandy
Reply
Report
0
0
Kaiser
Kaiser
This comment was minimized by the moderator on the site
Bonjour,
merci pour votre support. Cela répond presque à mon besoin.Par contre, comment faire quand la même valeur se trouve dans "Origine value" et "Replacing value" car la valeur modifiée redevient la valeur d'origine ?Exemple : Origine value    ->    Replacing value
 
13h - 21h  ->  5h - 13h
8h - 16h   -> 10h - 18h
10h - 18h  ->  8h - 16h

Merci par avance pour votre aide
Reply
Report
0
0
Cado
Cado
This comment was minimized by the moderator on the site
hello can we change also color ?  ex : i wanna change apple to red apple with the red filled cell
Reply
0
0
qill
qill
This comment was minimized by the moderator on the site
Is this method can be use if between sentence in a cell?e.g I want to change numbers between the line : abcdefghi 12345 jklmnopqrstu >>> abcdefghi 67891 jklmnopqrstu

if no is there any other method can be use? Please help. Thank you!
Reply
0
0
skyyang
skyyang     qill
This comment was minimized by the moderator on the site
Hello, qill,The code in this article can finish your problem, please try, thank you!

Reply
Report
0
0
Selina
Selina
This comment was minimized by the moderator on the site
<p>How would the code look like if you would check by line?



Example:



First line: Original value "Apple" (A1) and replacing value "Green Apple" (B1) and in the same line is

the original range to be replaced by B2 e.g., apple, orange, banana, nuts (D1, E1, F1,...)



Second line: Original value "Orange" (A2) and replacing value "Green Orange" (B2) and

in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2,...)



Thank you so much!





</p>
Reply
0
0
Selina
Selina
This comment was minimized by the moderator on the site
<p>Example:</p><p>Second line: Original value "Orange" (A2) and replacing value "Green Orange" (B2) and in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2,...)</p><p>Thank you so much!</p>
Reply
0
0
Aaron
Aaron
This comment was minimized by the moderator on the site
I tried copying this code into Excel so I can have it saved, but then if I copy from Excel into VBA, it doesn't work. Does anyone know why? As far as I can tell, the code is exactly the same.
Reply
0
0
Dee.Elli
Dee.Elli
This comment was minimized by the moderator on the site
My question didn't show completely. I am using Excel in Office 365, when I hit OK, nothing happens. Can anyone help?
Reply
Report
0
0
Dee.Elli
Dee.Elli
This comment was minimized by the moderator on the site
Hi, I am using the suggested code (see below) i
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

The values I need to replace are simple Country names, which need to be replaced by these:

Replacing Value
AD - Andorra
AE - United Arab Emirates
AF - Afghanistan
AG - Antigua and Barbuda
AI - Anguilla
AL - Albania
AM - Armenia
AO - Angola
AQ - Antarctica
AR - Argentina
AT - Austria
AU - Australia
AW - Aruba
AX - Aland Islands
AZ - Azerbaijan
BA - Bosnia and Herzegovina
BB - Barbados
BD - Bangladesh
BE - Belgium
BF - Burkina Faso
BG - Bulgaria
BH - Bahrain
BI - Burundi
BJ - Benin
BL - Saint Barthélemy
BM - Bermuda
BN - Brunei Darussalam
BO - Bolivia, Plurinational State of
BQ - Bonaire, Sint Eustatius and Saba
BR - Brazil
BS - Bahamas
BT - Bhutan
BV - Bouvet Island
BW - Botswana
BY - Belarus
BZ - Belize
CA - Canada
CC - Cocos (Keeling) Islands
CD - Congo, the Democratic Republic of the
CF - Central African Republic
CG - Congo
CH - Switzerland
CI - Cote d'Ivoire
CK - Cook Islands
CL - Chile
CM - Cameroon
CN - China
CO - Colombia
CR - Costa Rica
CU - Cuba
CV - Cape Verde
CW - Curaçao
CX - Christmas Island
CY - Cyprus
CZ - Czech Republic
DE - Germany
DJ - Djibouti
DK - Denmark
DM - Dominica
DO - Dominican Republic
DZ - Algeria
EC - Ecuador
EE - Estonia
EG - Egypt
EH - Western Sahara
ER - Eritrea
ES - Spain
ET - Ethiopia
FI - Finland
FJ - Fiji
FK - Falkland Islands (Malvinas)
FO - Faroe Islands
FR - France
GA - Gabon
GB - United Kingdom
GD - Grenada
GE - Georgia
GF - French Guiana
GG - Guernsey
GH - Ghana
GI - Gibraltar
GL - Greenland
GM - Gambia
GN - Guinea
GP - Guadeloupe
GQ - Equatorial Guinea
GR - Greece
GS - South Georgia and the South Sandwich Islands
GT - Guatemala
GW - Guinea-Bissau
GY - Guyana
HM - Heard Island and McDonald Islands
HN - Honduras
HR - Croatia
HT - Haiti
HU - Hungary
ID - Indonesia
IE - Ireland
IL - Israel
IM - Isle of Man
IN - India
IO - British Indian Ocean Territory
IQ - Iraq
IR - Iran, Islamic Republic of
IS - Iceland
IT - Italy
JE - Jersey
JM - Jamaica
JO - Jordan
JP - Japan
KE - Kenya
KG - Kyrgyzstan
KH - Cambodia
KI - Kiribati
KM - Comoros
KN - Saint Kitts and Nevis
KP - Korea, Democratic People's Republic of
KR - Korea, Republic of
KW - Kuwait
KY - Cayman Islands
KZ - Kazakhstan
LA - Lao People's Democratic Republic
LB - Lebanon
LC - Saint Lucia
LI - Liechtenstein
LK - Sri Lanka
LR - Liberia
LS - Lesotho
LT - Lithuania
LU - Luxembourg
LV - Latvia
LY - Libyan Arab Jamahiriya
MA - Morocco
MC - Monaco
MD - Moldova, Republic of
ME - Montenegro
MF - Saint Martin (French part)
MG - Madagascar
MK - Macedonia, the former Yugoslav Republic of
ML - Mali
MM - Myanmar
MN - Mongolia
MO - Macao
MQ - Martinique
MR - Mauritania
MS - Montserrat
MT - Malta
MU - Mauritius
MV - Maldives
MW - Malawi
MX - Mexico
MY - Malaysia
MZ - Mozambique
NA - Namibia
NC - New Caledonia
NE - Niger
NF - Norfolk Island
NG - Nigeria
NI - Nicaragua
NL - Netherlands
NO - Norway
NP - Nepal
NR - Nauru
NU - Niue
NZ - New Zealand
OM - Oman
PA - Panama
PE - Peru
PF - French Polynesia
PG - Papua New Guinea
PH - Philippines
PK - Pakistan
PL - Poland
PM - Saint Pierre and Miquelon
PN - Pitcairn
PS - Palestinian Territory, Occupied
PT - Portugal
PY - Paraguay
QA - Qatar
RE - Reunion
RO - Romania
RS - Serbia
RU - Russian Federation
RW - Rwanda
SA - Saudi Arabia
SB - Solomon Islands
SC - Seychelles
SD - Sudan
SE - Sweden
SG - Singapore
SH - Saint Helena, Ascension and Tristan da Cunha
SI - Slovenia
SJ - Svalbard and Jan Mayen
SK - Slovakia
SL - Sierra Leone
SM - San Marino
SN - Senegal
SO - Somalia
SR - Suriname
SS - South Sudan
ST - Sao Tome and Principe
SV - El Salvador
SX - Sint Maarten (Dutch part)
SY - Syrian Arab Republic
SZ - Swaziland
TC - Turks and Caicos Islands
TD - Chad
TF - French Southern Territories
TG - Togo
TH - Thailand
TJ - Tajikistan
TK - Tokelau
TL - Timor-Leste
TM - Turkmenistan
TN - Tunisia
TO - Tonga
TR - Turkey
TT - Trinidad and Tobago
TV - Tuvalu
TW - Chinese Taipei
TZ - Tanzania, United Republic of
UA - Ukraine
UG - Uganda
US - United States
UY - Uruguay
UZ - Uzbekistan
VA - Holy See (Vatican City State)
VC - Saint Vincent and the Grenadines
VE - Venezuela, Bolivarian Republic of
VG - Virgin Islands, British
VN - Viet Nam
VU - Vanuatu
WF - Wallis and Futuna
WS - Samoa
YE - Yemen
YT - Mayotte
ZA - South Africa
ZM - Zambia
ZW - Zimbabwe
Reply
Report
0
0
Brendan
Brendan
This comment was minimized by the moderator on the site
This is pretty cool. Is there a way that we could change it to work across the whole Workbook instead of a specific range?
Reply
0
0
sameer.x
sameer.x
This comment was minimized by the moderator on the site
Running into an issue when an Original Value is a subset of another, is there a way to look for Exact Match or another workaround? See attached image for example, result cell highlighted red is the issue. In case the image doesn't work, here's a typed out modified version:Original Value1: Call Option Risk | Replacing Value1: ^b^Call Option Risk^/b^
Original Value2: Index Call Option Risk | Replacing Value2: ^b^Index Call Option Risk^/b^[as you can see, OV2 contains OV1 but is not an exact match]
Result of MultiFindNReplace() on OV2: Index ^b^Call Option Risk^/b^ [but I'm expecting ^b^Index Call Option Risk^/b^]
Thanks
View Attachments (1 / 5)
Reply
Report
0
0
skyyang
skyyang     sameer.x
This comment was minimized by the moderator on the site
Hello, sameer,
May be the following code can solve your problem, please try it:

Sub MultiFindNReplace()

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, MatchCase:=True

Next

Application.ScreenUpdating = True

End Sub

Hope it can help you!
Reply
Report
0
0
Raghavan
Raghavan     skyyang
This comment was minimized by the moderator on the site
Thank you. It helped me and saved a lot of time.
Reply
0
0
sameer.x
sameer.x     skyyang
This comment was minimized by the moderator on the site
skyyang - Thanks. This version does not work in my case since it appears to 'match entire cell contents' but I need to replace only part of the string. I realize now that the example I typed out does not communicate this detail; can you see the JPG I attached instead? Original Range:
Product1 = Call Option: risk 1
Product2 = Index Call Option: risk 2

It's the extra text after the colon that breaks your version. And OP's code is able to ignore the extra text, but can't differentiate between "Call Option Risk:" and "Index Call Option Risk:". Again, I think the picture explains this best, if you could please take another look.
Reply
Report
0
0
ksec
ksec
This comment was minimized by the moderator on the site
How skip cell after first match?

I've sorted translations from longest phrase to shortest.
Reply
0
0
skyyang
skyyang     ksec
This comment was minimized by the moderator on the site
Hi, ksec,
Could you give more detailed information about your problem? Or you can insert a screenshot here to show your problem.
Thank you!
Reply
Report
0
0
mar_m
mar_m     skyyang
This comment was minimized by the moderator on the site
He means that if a part of the original repeats later there is an issue because the function replace again, sorted or not, if the original word is not unique (even a part of it) this function does not work
Reply
0
0
skyyang
skyyang     mar_m
This comment was minimized by the moderator on the site
Hello, mar_m,
Please apply the below code, try if it can solve your problem, Thank you!

Sub MultiFindNReplace()

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, MatchCase:=True

Next

Application.ScreenUpdating = True

End Sub
Reply
Report
0
0
A Davies
A Davies
This comment was minimized by the moderator on the site
Absolute hero! Thanks!
Reply
0
0
vikram
vikram
This comment was minimized by the moderator on the site
Thanks a ton.
Reply
0
0
chaoseisaladder@gmail.com
This comment was minimized by the moderator on the site
Hi!
i need this macro to work on specific columns without selecting different columns each time manually.
is there any way to mention desired columns and replacing table in the code?
appreciated
Reply
Report
0
0
Mert
Mert
This comment was minimized by the moderator on the site
what should i change to make the replacing range FIXED, i dont want to re-enter every time?
Reply
0
0
fan of this article
fan of this article
This comment was minimized by the moderator on the site
Thanks a lot, that works perfectly !
Reply
0
0
spoorthi
spoorthi
This comment was minimized by the moderator on the site
Thank YOU So much , it works like a magic ...saves my time.. thanks a ton :)
Reply
0
0
Tanay
Tanay
This comment was minimized by the moderator on the site
Hello. Thank you for the help. But it is not renaming the entire cell if it is a large string. For any cell which has more than 21 letters, it can only replace 21 letters and then else is same. Please help.
Reply
0
0
MURUGAN T G
MURUGAN T G
This comment was minimized by the moderator on the site
Hi im TG,Thank you for posting this page, It's very useful and make it very simple of my work and save more time , thank you sir....
Reply
0
0
Jairo L.
Jairo L.
This comment was minimized by the moderator on the site
Muchas Gracias me fue de mucha utilidad el codigo.
Reply
0
0
iris
iris
This comment was minimized by the moderator on the site
it works! THANK YOU SO MUCH!
Reply
0
0
Jon
Jon
This comment was minimized by the moderator on the site
Макрос не работает корректно !!! Пытался подобным образом заменить символы, но не различаются маленькие и большие буквы.
Reply
0
0
vertimai@gmail.com
This comment was minimized by the moderator on the site
Hi, I would like to replace whole cells in the entire Excel file (with many sheets). What should be replace in the Original Range to do that? Thanks.
Reply
Report
0
0
Patricia Quinsler
Patricia Quinsler    
This comment was minimized by the moderator on the site
function runReplaceInSheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
Reply
0
0
Ugne
Ugne
This comment was minimized by the moderator on the site
What should be replaced in the code to run it on all the Excel file's sheets?
Reply
0
0
tsun1108@gmail.com
This comment was minimized by the moderator on the site
Can I ask if I want to replace the entire cell based on partial match, how should I alter the code?
For example: I want to code all cells containing the word apple to 1 (regardless whether it's "green apple" or "red apple"), I want all of them to turn into 1.
Reply
Report
0
0
Carlos
Carlos
This comment was minimized by the moderator on the site
El mejor!!! Mil gracias por compartir estos conocimientos, fue increíble la ayuda que me proporciono este programa en VBasic (nunca lo había usado hasta ahora), lo pensaba realizar en php o similar pero leyendo encontre esta valiosa información.

Gracias!
Reply
0
0
Abhishek
Abhishek
This comment was minimized by the moderator on the site
The VBA method of find and replace is not working. It was working for me till yesterday but today I am not able to do any find and replace using it today. Can you guys please help
Reply
0
0
skyyang
skyyang     Abhishek
This comment was minimized by the moderator on the site
Hi, Abhishek,
After inserting the code into the workbook, you should save the workbook as Excel Macro-Enabled Workbook format to keep the code without losing it.
Please try it, hope this can help you!
Reply
Report
0
0
Am
Am
This comment was minimized by the moderator on the site
This is causing my original values data to also be replaced, in addition to the column of data I want to be replaced. How do I stop this?
Reply
0
0
Joe
Joe
This comment was minimized by the moderator on the site
Thanks, this worked perfectly.
Reply
0
0
thapaliya233
thapaliya233
This comment was minimized by the moderator on the site
Hi

I have to replace list of bank with proper name based on key words. For example, if somebody enters Halifax, it should be Halifax Bank of Scotland (HBOS) or if somebody enters LloydsTSB, it should be Lloyds.

Original value Replacing Value
LloydsTSB : Lloyds
Santander : Santander (Abbey)
Argos Card Services : Argos
Halifax : Halifax Bank of Scotland (HBOS)

The down formula works in many cases. But if there is two Halifax bank on my list, I will get Halifax Bank of Scotland (HBOS) (HBOS)
. So, first time it finds Halifax, replace with correct one. Again it finds Halifax and replace. How can I correct this problem ?


Sub FindReplaceNew()
Dim xTitledId As String
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitledId = "Test"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range", xTitledId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitledId, 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 = False


End Sub
Reply
Report
0
0
David Gaertner
David Gaertner
This comment was minimized by the moderator on the site
I modified this to work on Microsoft Excel for Mac 2016 and to replace strings that are within whole words. Here are the first and last lines I changed (along with the stuff in between that I didn't change).
Set InputRng = Application.InputBox(Prompt:="Original Range :", Title:="Range to search", Default:=InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox(Prompt:="Replace Range :", Title:="Replace mapping range", 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:=xlPart
Reply
0
0
etilyeti
etilyeti
This comment was minimized by the moderator on the site
Hello, how can I only replace full words (and not strings within a word) ? By word, I mean a string that is at the beginning/end of the cell or preceded/followed with a space.
Reply
Report
0
0
khamir007
khamir007     etilyeti
This comment was minimized by the moderator on the site
Use this code.


Sub MultiFindNReplace()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "MultipleReplaceValue"
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, MatchCase:=True
Next
Application.ScreenUpdating = True
End Sub
Reply
Report
0
0
Sumeet Bhandari
Sumeet Bhandari     khamir007
This comment was minimized by the moderator on the site
Thanks a Ton man.It worked just fine.
Reply
0
0
skyyang
skyyang     etilyeti
This comment was minimized by the moderator on the site
Hello, Etienne,
Can you give an example screenshot for your problem?
Thank you!
Reply
Report
0
0
Etienne
Etienne
This comment was minimized by the moderator on the site
Hello, how can I replace only full words. Replace only if the string is at the beginning/end of the cell or preceded/followed by a space ?
Reply
0
0
pratik
pratik
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

InputRng.Replace
Rng.Value, Rng.Offset(0, 1).Value

Next

MsgBox "Done Successfully....", vbInformation

Application.ScreenUpdating = True

End Sub
Reply
0
0
ramanuj sharma
ramanuj sharma
This comment was minimized by the moderator on the site
THANKS A LOT.....
ITS MOST EFFECTIVE CODE TO USE....


THANKS SIR,
Reply
0
0
francesco
francesco
This comment was minimized by the moderator on the site
how do I implement "match entire cell content" in this code?
Reply
0
0
b.chevreau@gmail.com
    francesco
This comment was minimized by the moderator on the site
in case someone else is looking for this, here it is:

Replace this line
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value

by
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlWhole



It will look for the whole cell in target and original, and replace by a whole cell instead of particular instances of doubles inside a cell
Reply
Report
0
0
Jorge Vargas
Jorge Vargas
This comment was minimized by the moderator on the site
don't work in all the columns for me idk why
Reply
0
0
Hans Filbert
Hans Filbert
This comment was minimized by the moderator on the site
Perfect Solution.
Easy to use.
Reply
0
0
Kalle
Kalle
This comment was minimized by the moderator on the site
Hi, this was very powerful and useful code. Thanks a lot! There is just a couple of features lacking and it would be perfect for me: 1) Could it be possible to modify the code so that it would process multiple files at once? For example you have your find_replace attributes in different file and then you just name the files in the VBA code you want to modify. I dunno...That would be cool. 2) Could it be possible to make a report so that I would know what attributes were replaced. So that I could double-check if something went wrong.
Reply
0
0
Roshan
Roshan
This comment was minimized by the moderator on the site
this is very useful .. Thanks You!!
Reply
0
0
dSd
dSd
This comment was minimized by the moderator on the site
Excellent work!!!!!
This saved me after my enterprise free period got over!
Reply
0
0
karel
karel
This comment was minimized by the moderator on the site
wohoo, made my day. thank you
Reply
0
0
styleguerilla
styleguerilla
This comment was minimized by the moderator on the site
How can I make this VBA macro work for a formula, e.g. if I want to replace 'Sheet1!$A:$A' with Table1[A]?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations