Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word macro needed ~ multiple search and replace (Word 2003)

    I'm hoping someone has a code snippet to search and replace multiple words in an existing document (so we cannot use auto correct as we go).

    Ideally to look up the replace list written in a separate Word (or Excel) document (so it is easily maintainable).

    e.g.

    Source
    users {users|members|clientele|web masters|organisations}
    maintain {maintain|cherish|foster|preserve|safeguard|tend|w atch}
    own {private|personal|confidential|own}
    web {sites|webs|pages}

    Original

    So this allows users to maintain their own web.

    Replacement

    So this allows {users|members|clientele|Web masters|organisations} to {maintain|cherish|foster|preserve|safeguard|tend|w atch} their {private|personal|confidential|own} {sites|webs|pages}.

    A total ideal (probably impossible) would be to maintain case i.e.

    Users returns {Users|Members|Clientele|Web Masters|Organisations}
    user returns {users|members|clientele|web masters|organisations}

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    Welcome to Woody's Lounge!

    The attached zip file contains two files:
    - A text file Code.txt with the text of a macro.
    - An Excel workbook Replacements.xls with the words to replace in column A and their replacements in column B.

    You can copy the code from the text file into a module. You'll have to modify the constant

    Const strXLFile = "H:ExcelReplacements.xls"

    so that it points to the folder into which you unzipped the workbook.

    Important: you must be very careful with the sort order of the list of words and replacements. for example, you want to replace "users" with a list in which the word "web" occurs, and you also want to replace the word "web" with a list of words. If "web" comes after "users" in the worksheet, you'll get unintended results: "users" will be replaced with "{users|members|clientele|{sites|webs|pages} masters|organisations}". So "users" should come *after* "web" in the worksheet.

    If a word is in ALL CAPS, the replacement will be in ALL CAPS too, but the macro doesn't preserve Proper Case, that would probably require a lot more work.
    Attached Files Attached Files

  4. #3
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    WOW! So fast!! I am so impressed. I'll download and test ~ thank you very much. Paul

  5. #4
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    OK, I've tested and it all works well. Your point about the order is well made however I've changed the spreadsheet to be
    <space> first word <space> and <space> replacement text <space>
    eg [ own ] and [ {private|personal|confidential|own} ]
    Therefore nothing in the replacement text matches the search terms (because there are no spaces in there).

    Thanks for this, it is EXACTLY what I wanted.
    Regards Paul

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    > I've changed the spreadsheet to be <space> first word <space> and <space> replacement text <space>

    That's a good workaround. Glad to have been able to help.

  7. #6
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    HansV, I submit the above to an online program which converts the part in {} into an article. i.e.

    So this allows {users|members|clientele|Web masters|organisations} to {maintain|cherish|foster|preserve|safeguard|tend|w atch} their {private|personal|confidential|own} {sites|webs|pages}.

    Could be: So this allows members to watch their personal sites. So this allows users to preserve their personal pages.

    It would be handy to do this function within Word and avoid going online ~ i.e. keep the original (with full braces) and generate a 2nd document with the decoded text. There will be dozens of braces to a document, some containing two terms, some containing multiple terms. The criteria is to be totally random ~ i.e. rerun the macro and get a totally different decoded document (some words may be the same, especially if there is only a choice of two, but the sentence as a whole will be random).

    Regards Paul

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    Instead of first replacing words with bracketed lists, for example users > {users|members|clientele|Web masters|organisations}, and then replacing the bracketed list with a randomly selected item from it, it might be more efficient to replace each of the specified words with a random item from the corresponding list directly, for example users > clientele.

    Would that work for you, or do you prefer to keep the two steps?

  9. #8
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    It would work as one step ~ I had not even thought of doing it that way !!
    It would still be good to keep a copy of the original document and generate a 2nd version though.
    Your very quick at responding!

    Regards Paul

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    The attached text file contains a modified macro that will copy the contents of the active document to a new document and replace the keywords with entries randomly selected from the corresponding list in the Excel workbook.
    Attached Files Attached Files

  11. #10
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    Thanks Hans,

    You can write code faster than I can type :-)

    It's not quite there ~ the strings are concatinated and at least one } is visible, however you have given me enough to build on.

    So this allows users to maintain their own web.

    So this allowsmemberstopreservetheirown web.
    So this allowsclienteletowatch}theirown web.
    So this allowsclienteletosafeguardtheirown web.
    So this allowsclienteletosafeguardtheirown web.

    Very many thanks Paul

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    You'll have to fine tune the code for your situation. I wrote the code for the sample workbook that I attached higher up in this thread, where the entries in column A and B don't have spaces before and after them.

  13. #12
    Lounger
    Join Date
    Apr 2008
    Location
    Colchester, Essex, United Kingdom
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word macro needed ~ multiple search and replace (Word 2003)

    Just to close the thread ~ I've now got two working macros that do all I need ~ thanks Hans, I would not have reached this stage without your assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •