#1
|
|||
|
|||
Find, Replace using Excel List
The code in https://www.msofficeforums.com/word-...d-replace.html has been really helpful to me. But I am facing difficulty in case of case sensitivity. The word gets replaced only if it matches the case. The lengthier solution to this problem is having the word with all possible cases (e.g. all caps, or all small or capitalising each word) in the worksheet. Can we do any modification to this code, so that the words gets replaced even if their case did not match and the replaced word remains in same case as the word being replace (like when we do it with MS word's replace function)?, if so, please help me with this.
Thanks Regards |
#2
|
||||
|
||||
The simple solution is to change:
.MatchCase = True to: .MatchCase = False
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
What about case sensitivity
Thank you Paul. It worked perfectly.
|
#4
|
|||
|
|||
Is there a way to change this macro so that the search and replace terms are in a two column word table instead of a spreadsheet? I ask because I need to do a search and replace involving Word Fields and I'm unsure whether that those can be accurately stored in a spreadsheet. I feel like I found a macro like that a while back and have somehow lost it. But perhaps it was actually this macro.
KS |
#5
|
||||
|
||||
See, for example: https://www.msofficeforums.com/148925-post2.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Issue with plurals
I have been using the macro code in the link for find and replace related requirements. Thank you for promptly replying to my last query.
I have another follow up query. Currently, the code does not find and replace plurals. E.g. it replaces the word 'Syllable' to 'Syl', as defined in database. But if the document has word 'Syllables', it ignores the plural. Ideally, it should replace the word with 'Syls'. The longer solution is to include plural words into the database as well, but that increases the processing time. Is there any way of implementing this feature? thanks |
#7
|
||||
|
||||
You could change:
.MatchWholeWord = True to: .MatchWholeWord = False but this might have quite undesirable effects. For example, if the found word also forms part of longer, unrelated, words, those might get updated too. You might get better results inserting: .MatchAllWordforms = True after: .Replacement.ClearFormatting but that, too can have undesirable results. Test both on a document, with: .Replacement.Highlight = True inserted after: .Replacement.ClearFormatting and set your preferred highlight colour before running the macro. If you don't get the desired results, you'll need to insert an additional F/R expression for each plural.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Issue with plurals
Hi paul,
as suggested by you, I have tried incorporating the new code. Clearly, MatchWholeWord=False doesn't give desired results. Moving on to .MatchAllWordForms=True, in spite of all types of changes in my test file, database or even some lines of code, it gives a run-time error code 5610, stating that "the find what text for find all word forms can search only alphabetic letters". My test file has following line, "The school technical library issues Mathematics, Physics and chemistry books to the students and teachers". The database has following words to be replaced with :- School to Sch, Library to lib, Technical to Tech, Mathematics to Maths, Physics to Phy, Chemistry to Chem, student to stdnt and teachers to tchr. The required result must be "The sch tech lib issues Maths, Phy and chem books to the stdnts and tchrs" I have read online that this function only works with english language and that it wont work along with .MatchWholeWord=True or .MatchCase=True. But even including these changes has not helped. |
#9
|
||||
|
||||
As previously suggested, you might need to process singular & plural forms alike:
Code:
Sub BulkFindReplace() Application.ScreenUpdating = False Dim FList As String, RList As String, j As Long FList = "School,Library,Technical,Mathematics,Physics,Chemistry,students,student,teachers,teacher" RList = "Sch,lib,Tech,Maths,Phys,Chem,stdnts,stdnt,tchrs,tchr" With ActiveDocument.Range.Find .ClearFormatting .Replacement.ClearFormatting .Format = False .Forward = True .MatchCase = False .MatchWholeWord = True 'Process each word from the Find/Replace Lists For j = 0 To UBound(Split(FList, ",")) .Text = Split(FList, ",")(j) .Replacement.Text = Split(RList, ",")(j) .Execute Replace:=wdReplaceAll Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Issue with plurals
Alright Paul,
I guess that is the only solution then. thanks for your help. Cheers! |
#11
|
|||
|
|||
Issue with plurals
Hi Paul,
I have found the solution for my problem. The issue with .MatchAllWordForms is that it does not work if the word/string consists of more than a word. So, using instr(.text, " "), i checked if the .text from xlFlist has space or not. If yes, then, I used .MatchWholeWord=True, else, I used MatchAllWordForms=true. The results of this simple if else construct gave the answer. thus, there is no requirement of creating a separate F/R list of plurals. THE CODE I USED IS COPIED BELOW :- Code:
With wdDoc With .Range.Find .ClearFormatting .Replacement.ClearFormatting .Replacement.Highlight = True .MatchCase = False .Wrap = wdFindContinue For i = 1 To UBound(Split(xlFList, "|")) .Text = Split(xlFList, "|")(i) 'checking if checked word has spaces i.e. it comprises of multiple words If InStr(.Text, " ") = 0 Then .MatchWholeWord = False .MatchAllWordForms = True .Replacement.Text = Split(xlRList, "|")(i) .Execute Replace:=wdReplaceAll Else .MatchAllWordForms = False .MatchWholeWord = True .Replacement.Text = Split(xlRList, "|")(i) .Execute Replace:=wdReplaceAll End If Next End With Regards Last edited by macropod; 02-26-2020 at 04:10 AM. Reason: Added code tags |
#12
|
||||
|
||||
Well, yes, but you never said the Find strings might be more than one word. You only ever mentioned an issue with plurals...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
Yes Paul. I forgot to mention that..please excuse me for that.
|
#14
|
|||
|
|||
Quote:
|
#15
|
||||
|
||||
After:
i As Long insert: , Hlite As Long on the same line. After: strFile = Dir(strFolder & "\*.doc", vbNormal) insert new lines with: 'Set the preferred highlight colour Hlite = Options.DefaultHighlightColorIndex Options.DefaultHighlightColorIndex = wdBrightGreen After: .Replacement.ClearFormatting insert new lines with: .Replacement.Highlight = True .Format = True After: Set wdDoc = Nothing insert new lines with: 'Restore the original highlight setting Options.DefaultHighlightColorIndex = Hlite
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find and replace multiple values, according to table value - excel formula | EtanM | Excel Programming | 3 | 04-11-2016 01:43 AM |
Find and Replace different in Excel 2010? | admyers | Excel | 1 | 09-16-2015 10:28 AM |
Find/Replace is not working - Excel 2010 | fieldhaven | Excel | 2 | 02-19-2014 08:30 AM |
Find - Replace Macro using a table list | mdw | Word | 0 | 08-01-2013 04:36 PM |
Find and Replace using Excel range | dmarie123 | Word VBA | 15 | 04-02-2013 07:54 AM |