Note: The other languages of the website are Google-translated. Back to English
Mewngofnodi  \/ 
x
or
x
Cofrestru  \/ 
x

or

Sut i ddod o hyd i werthoedd lluosog a'u disodli ar unwaith yn Excel?

doc lluosog dod o hyd i ddisodli 1

Fel y gwyddom i gyd, gallwn ddefnyddio swyddogaeth Dod o Hyd ac Amnewid i ddod o hyd i luosog yr un gell a rhoi gwerth yn ôl yr angen. Ond weithiau, mae angen i chi gymhwyso amnewidiad llawer i lawer ar yr un pryd. Er enghraifft, mae gen i ystod o ddata, a nawr rydw i eisiau disodli'r holl Afalau yn Afalau Coch, Orennau i Orennau Gwyrdd, Bananas i Bananas Melyn ac yn y blaen fel y dangosir sgrinluniau canlynol, a oes gennych chi unrhyw syniadau da i ddatrys y dasg hon Excel?

Darganfyddwch a disodli gwerthoedd lluosog ar unwaith gyda chod VBA


swigen dde glas saeth Darganfyddwch a disodli gwerthoedd lluosog ar unwaith gyda chod VBA

Os ydych wedi blino dod o hyd i'r gwerthoedd a'u disodli dro ar ôl tro, gall y cod VBA canlynol eich helpu i ddisodli gwerthoedd lluosog gyda'ch testunau angenrheidiol ar unwaith.

1. Crëwch eich amodau yr ydych am eu defnyddio sy'n cynnwys y gwerthoedd gwreiddiol a'r gwerthoedd newydd. Gweler y screenshot:

doc lluosog dod o hyd i ddisodli 2

2. Yna dal i lawr y ALT + F11 allweddi i agor y Ffenestr Microsoft Visual Basic for Applications.

3. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn ffenestr y Modiwl.

Cod VBA: Darganfyddwch a disodli gwerthoedd lluosog ar unwaith

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. Yna pwyswch F5 allwedd i redeg y cod hwn, yn y blwch prydlon popped allan, nodwch yr ystod ddata yr ydych am gael gwerthoedd newydd yn lle'r gwerthoedd.

doc lluosog dod o hyd i ddisodli 3

5. Cliciwch OK, ac mae blwch prydlon arall yn cael ei arddangos i'ch atgoffa i ddewis y meini prawf rydych chi'n cael eich creu yng ngham 1. Gweler y screenshot:

doc lluosog dod o hyd i ddisodli 4

6. Yna cliciwch OK, mae'r gwerthoedd newydd wedi'u disodli gan y gwerthoedd newydd yn ôl yr angen.

doc lluosog dod o hyd i ddisodli 5


Erthyglau cysylltiedig:

Sut i ddod o hyd i destun penodol a'i ddisodli mewn blychau testun?

Sut i ddod o hyd i destun mewn teitlau siart yn Excel?

Sut i ddod o hyd i destun a'i ddisodli o fewn sylwadau yn Excel?

Sut i newid sawl llwybr hypergyswllt ar unwaith yn Excel?


Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    qill · 22 days ago
    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!
    • To post as a guest, your comment is unpublished.
      skyyang · 18 days ago
      Hello, qill,
      The code in this article can finish your problem, please try, thank you!

  • To post as a guest, your comment is unpublished.
    Selina · 1 months ago

    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!


  • To post as a guest, your comment is unpublished.
    Selina · 1 months ago

    Example:

    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!

  • To post as a guest, your comment is unpublished.
    Aaron · 1 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Dee.Elli · 6 months ago
    My question didn't show completely. I am using Excel in Office 365, when I hit OK, nothing happens. Can anyone help?
  • To post as a guest, your comment is unpublished.
    Dee.Elli · 6 months ago
    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
  • To post as a guest, your comment is unpublished.
    Brendan · 1 years ago
    This is pretty cool. Is there a way that we could change it to work across the whole Workbook instead of a specific range?
  • To post as a guest, your comment is unpublished.
    sameer.x · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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!

      • To post as a guest, your comment is unpublished.
        Raghavan · 11 months ago
        Thank you. It helped me and saved a lot of time.
      • To post as a guest, your comment is unpublished.
        sameer.x · 1 years ago
        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.
  • To post as a guest, your comment is unpublished.
    ksec · 1 years ago
    How skip cell after first match?

    I've sorted translations from longest phrase to shortest.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, ksec,
      Could you give more detailed information about your problem? Or you can insert a screenshot here to show your problem.
      Thank you!
      • To post as a guest, your comment is unpublished.
        mar_m · 1 years ago
        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


        • To post as a guest, your comment is unpublished.
          skyyang · 1 years ago
          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
  • To post as a guest, your comment is unpublished.
    A Davies · 2 years ago
    Absolute hero! Thanks!
  • To post as a guest, your comment is unpublished.
    vikram · 2 years ago
    Thanks a ton.
  • To post as a guest, your comment is unpublished.
    chaoseisaladder@gmail.com · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Mert · 2 years ago
    what should i change to make the replacing range FIXED, i dont want to re-enter every time?
  • To post as a guest, your comment is unpublished.
    fan of this article · 2 years ago
    Thanks a lot, that works perfectly !
  • To post as a guest, your comment is unpublished.
    spoorthi · 2 years ago
    Thank YOU So much , it works like a magic ...saves my time.. thanks a ton :)
  • To post as a guest, your comment is unpublished.
    Tanay · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    MURUGAN T G · 2 years ago
    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....
  • To post as a guest, your comment is unpublished.
    Jairo L. · 2 years ago
    Muchas Gracias me fue de mucha utilidad el codigo.
  • To post as a guest, your comment is unpublished.
    iris · 2 years ago
    it works! THANK YOU SO MUCH!
  • To post as a guest, your comment is unpublished.
    Jon · 2 years ago
    Макрос не работает корректно !!! Пытался подобным образом заменить символы, но не различаются маленькие и большие буквы.
  • To post as a guest, your comment is unpublished.
    vertimai@gmail.com · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Ugne · 3 years ago
    What should be replaced in the code to run it on all the Excel file's sheets?
  • To post as a guest, your comment is unpublished.
    tsun1108@gmail.com · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Carlos · 3 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Abhishek · 3 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      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!
  • To post as a guest, your comment is unpublished.
    Am · 3 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Joe · 3 years ago
    Thanks, this worked perfectly.
  • To post as a guest, your comment is unpublished.
    thapaliya233 · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    David Gaertner · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    etilyeti · 3 years ago
    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.
    • To post as a guest, your comment is unpublished.
      khamir007 · 3 years ago
      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
      • To post as a guest, your comment is unpublished.
        Sumeet Bhandari · 2 years ago
        Thanks a Ton man.It worked just fine.
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Etienne,
      Can you give an example screenshot for your problem?
      Thank you!
  • To post as a guest, your comment is unpublished.
    Etienne · 3 years ago
    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 ?
  • To post as a guest, your comment is unpublished.
    pratik · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    ramanuj sharma · 3 years ago
    THANKS A LOT.....
    ITS MOST EFFECTIVE CODE TO USE....


    THANKS SIR,
  • To post as a guest, your comment is unpublished.
    francesco · 3 years ago
    how do I implement "match entire cell content" in this code?
    • To post as a guest, your comment is unpublished.
      b.chevreau@gmail.com · 3 years ago
      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
  • To post as a guest, your comment is unpublished.
    Jorge Vargas · 4 years ago
    don't work in all the columns for me idk why
  • To post as a guest, your comment is unpublished.
    Hans Filbert · 4 years ago
    Perfect Solution.
    Easy to use.
  • To post as a guest, your comment is unpublished.
    Kalle · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Roshan · 4 years ago
    this is very useful .. Thanks You!!
  • To post as a guest, your comment is unpublished.
    dSd · 4 years ago
    Excellent work!!!!!
    This saved me after my enterprise free period got over!
  • To post as a guest, your comment is unpublished.
    karel · 4 years ago
    wohoo, made my day. thank you
  • To post as a guest, your comment is unpublished.
    styleguerilla · 4 years ago
    How can I make this VBA macro work for a formula, e.g. if I want to replace 'Sheet1!$A:$A' with Table1[A]?
  • To post as a guest, your comment is unpublished.
    Jason Ebensberger · 4 years ago
    This is AWESOME!!! Very simple solution to my problem. THANK YOU!
  • To post as a guest, your comment is unpublished.
    sohberson · 4 years ago
    in case anybody still struggles with replacing entire cell value and not just part: use Lookat:=xlWhole

    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:=Rn g.Offset(0, 1).Value, Lookat:=xlWhole
    Next
    Application.ScreenUpdating = True
    End Sub
    • To post as a guest, your comment is unpublished.
      JProf · 4 years ago
      Was looking for this specifically - thank you!
  • To post as a guest, your comment is unpublished.
    Nathan Rona · 4 years ago
    thanks, you're great. Your script saved me at least half an hour of find and replace :lol:
  • To post as a guest, your comment is unpublished.
    Avril · 4 years ago
    This tutorial was excellent! Did what I needed it to do.

    Is there a way to automatically run this macro on specific columns every time I open it/after pressing a key or something easy? After I've already saved the macro on a macro enabled file. I am creating a document to share with my colleagues and I'm not sure everyone will follow all the steps.

    Thank you!
  • To post as a guest, your comment is unpublished.
    Jin · 4 years ago
    xxx 1
    xxx 2
    xxx 3
    xxx 4
    xxx 5
    xxx 6

    Sir, how can i replace 1, 2, 3, 4, 5, 6 into xxx in a very short period of time?
  • To post as a guest, your comment is unpublished.
    David · 4 years ago
    This VBA script just saved me hours if not days of find and replace data entry. Thanks for sharing :)
  • To post as a guest, your comment is unpublished.
    Christopher Witmer · 4 years ago
    This saved me hours of work. Thank you soooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo much.
  • To post as a guest, your comment is unpublished.
    Jeremiah H · 4 years ago
    I LOVE this formula and use it all the time!

    I need a very similar formula but can't work out the logic.

    Column A is full cells with lots of text.

    Column B is full of my 'keywords' that I want to extract, much like the 'Original Value' in this formula.

    Column C needs to be the words from Column A that match the 'keywords' in Column B. Ideally they would be separated by commas with NO spaces, but I can use tools to insert these.

    Thanks, guys, your stuff is amazing!
  • To post as a guest, your comment is unpublished.
    Siddharth Gadia · 4 years ago
    How to do it for Exact Cell match?? if not exact, it should leave it.