Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
Excel 2003, Windows XP
This code, based in an Excel workbook, retrieves words from Microsoft Word (Word 2003). But first it strips out all punctuation. I'm having trouble stripping out the wildcard characters. This is the code fragment not working: Set oRange = ActiveDocument.Range With oRange.Find .Text = "?" .Replacement.Text = " " .Forward = True .Wrap = wdFindContinue End With oRange.Find.Execute Replace:=wdReplaceAll I've tried replacing .Text = "?" with .Text = "/?" which is the MSWord signal to look for the actual character mark, and .Text = "~?" which is the Excel signal to look for the actual character mark, but neither works. Any ideas? I also need this to find the MSWord wildcard characters ! and *. Thank you! Susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
I use the following function whenever I need to filter unwanted
characters from strings. Perhaps you will find it helpful... Function FilterString$(ByVal TextIn As String, _ Optional IncludeChars As String, _ Optional IncludeLetters As Boolean = True, _ Optional IncludeNumbers As Boolean = True) ' Filters out all unwanted characters in a string. ' Arguments: TextIn The string being filtered. ' IncludeChars [Optional] Keeps any characters. ' IncludeLetters [Optional] Keeps any letters. ' IncludeNumbers [Optional] Keeps any numbers. ' ' Returns: String containing only the wanted characters. Const sSource As String = "FilterString()" 'The basic characters to always keep Const sLetters As String = "abcdefghijklmnopqrstuvwxyz" Const sNumbers As String = "0123456789" Dim i As Long, CharsToKeep As String CharsToKeep = IncludeChars If IncludeLetters Then _ CharsToKeep = CharsToKeep & sLetters & UCase(sLetters) If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers For i = 1 To Len(TextIn) If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) Next End Function 'FilterString() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
Thanks, Garry, I'll keep that in mind!
Susan On Sunday, September 8, 2013 2:06:16 PM UTC-4, GS wrote: I use the following function whenever I need to filter unwanted characters from strings. Perhaps you will find it helpful... Function FilterString$(ByVal TextIn As String, _ Optional IncludeChars As String, _ Optional IncludeLetters As Boolean = True, _ Optional IncludeNumbers As Boolean = True) ' Filters out all unwanted characters in a string. ' Arguments: TextIn The string being filtered. ' IncludeChars [Optional] Keeps any characters. ' IncludeLetters [Optional] Keeps any letters. ' IncludeNumbers [Optional] Keeps any numbers. ' ' Returns: String containing only the wanted characters. Const sSource As String = "FilterString()" 'The basic characters to always keep Const sLetters As String = "abcdefghijklmnopqrstuvwxyz" Const sNumbers As String = "0123456789" Dim i As Long, CharsToKeep As String CharsToKeep = IncludeChars If IncludeLetters Then _ CharsToKeep = CharsToKeep & sLetters & UCase(sLetters) If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers For i = 1 To Len(TextIn) If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) Next End Function 'FilterString() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
You're welcome!
Not that there are some characters you'll want to include that occur at the end of a sentance or paragraph as valid punctuation. These should be included in the string passed for IncludeChars... "!.? " ...where you'll also include any other special characters. You'll need to modify the loop to include a check that the question character (which may be a wildcard) is not valid punctuation... For i = 1 To Len(TextIn) If Mid$(TextIn, i, 2) = "? " Or _ Mid$(TextIn, i, 3) = "?" & vbCrLf Then FilterString = FilterString & "~" Else If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) End If Next FilterString = Replace(FilterString, "~", "?") -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
Typo!
You're welcome! Note that there are some characters you'll want to include that occur at the end of a sentance or paragraph as valid punctuation. These should be included in the string passed for IncludeChars... "!.? " ..where you'll also include any other special characters. You'll need to modify the loop to include a check that the question character (which may be a wildcard) is not valid punctuation... For i = 1 To Len(TextIn) If Mid$(TextIn, i, 2) = "? " Or _ Mid$(TextIn, i, 3) = "?" & vbCrLf Then FilterString = FilterString & "~" Else If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) End If Next FilterString = Replace(FilterString, "~", "?") -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
Thanks again, Garry! I did keep your helpful code on hand, but for me, this time, I decided it was easier to use the ASCII codes for the particular characters in the Word document (^nnn where "nnn" is the ASCII code) in the Find/Replace coding. It works perfectly.
In the grand scheme of things, your code probably works better, but I already had it set up a certain way and this worked. Thank you again for your help! Warmly, Susan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace MSWord wildcard characters from within Excel
Thanks again, Garry! I did keep your helpful code on hand, but for
me, this time, I decided it was easier to use the ASCII codes for the particular characters in the Word document (^nnn where "nnn" is the ASCII code) in the Find/Replace coding. It works perfectly. In the grand scheme of things, your code probably works better, but I already had it set up a certain way and this worked. Thank you again for your help! Warmly, Susan Thanks for the feedback! ..much appreciated! Best wishes in your endeavors... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using wildcard characters in find & replace | Excel Worksheet Functions | |||
Use of Wildcard characters with replace | Excel Discussion (Misc queries) | |||
Find and Replace Command Using Wildcard Help | Excel Discussion (Misc queries) | |||
Find Replace Wildcard | New Users to Excel | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) |