Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Find (97 SR2)

    How can I do an OR conditional find?

    Selection.Find.Text = "^#^#^#^#^#-" Or "^#^#^#^#^#^p"

    That syntaxdoesn't work. What I want to do is find 5 digits
    with either a hyphen or a paragraph mark as the next
    character.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. 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
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    How about

    [0-9]{5}[^13^11^45]
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    What if you want to find completely different text
    e.g.1 Fred OR Barney OR Wilma OR Betty Or R2D2 Or 1AS-%X?

    Also, what about if I want to find the opening square bracket "[" then 4 digits then another opening square bracket "[" then 1 of 3 specific letters A, B or Z, followed by a digit or an x then a closing bracket "]" then a colon then 1 to 3 digits or letters then a closing bracket "]" e.g. [1234[A1]:24V] and [4739[Bx]:96] would both match.

    How do I get around the confusion of [0-9] and "[" itself - the wildcards and special characters seem to get in each others way.

    I would really like to be able to automate a search on the basis of either a template such as described above and more importantly using the OR clause somehow. I know I could recursively call the search with varing .find.text values but is there another way? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  5. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Thanks for posting. This request dates back to my quest to find a simple but powerful way to reliably locate an address in a document. I finally settled on

    "[,][ ][A-Z]{2}[ ]{1,2}[0-9]{5}"

    as the findtext of a Find. As long as the state (USA only) is abbreviated in two characters, it works pretty good.

    If you want find to find a square bracket include two in your criteria "[".

    The idea of implementing logical OR in the findtext is intriguing to me and I think I've accomplished it for my needs using the wildcards (see Word help on wildcards).

    The above findtext reads like this: find the first comma that is followed by a space then followed by two capital letters then 1 or 2 spaces then 5 digits. That combo would be rare if it were NOT an address. Your thoughts?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #5
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Kevin,
    first a solution to my square bracket problem - I'm not sure what you meant by "include two in your criteria" as I tried a variety of ways to do this without success.
    However the following worked when I used the backslash character, (should have read the help a little more slowly) <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    "[[0-9]{4}:[ABS]{1}[0-9x]{1}:[0-9]{3}]" to find a string such as [1234:B3:567].

    Another aspect is how you might use wildcards to search for varying length different words, e.g.
    shall, must, may, should, could without resorting to repeat searches. I've tried combining the wildcard criteria without success so far.

    The findtext value of
    "[cms]{1}[ahou]{1}[aosuy]{1}[ltu]{0,1}[l]{0,1}[d]{0,1}"
    doesnt work as it seems that 0 is an invalid count value for this wildcard. Without the indexes it only finds "should". I've also tried combining range and prefix without success.

    Dansy

  7. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    I was mis-thinking on the double bracket thing. You're right, it's the backslash then the reserved symbol to find the symbol in the text. Sorry.

    There is an exclusivity to the use of wildcards v. special characters. You have to pick. However, some cross-over exists via alternative sytnax. You can, for instance, search for the paragraph mark using wildcards by including [^13] in the findtext.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    You can use the RegExp object from VBScript to use "Or" with wildcards. You can include it by checking "Microsoft VBScript Regular Expressions (5.5)" in "Tools > References" in the VB editor.

    An example from the VBScript help file: Type some text containing "apple" and "pear" (capitalization doesn't matter, "Apples" will also be found), then select the text and run the macro "TestRegExp".

    <pre>Sub TestRegExp()
    ' | stands for "or"
    MsgBox (RegExpTest("pear|apple", Selection.text))
    End Sub

    Function RegExpTest(patrn, strng)
    Dim RetStr
    Dim regEx, Match, Matches ' Create variable.
    Set regEx = New regExp ' Create a regular expression.
    regEx.Pattern = patrn ' Set pattern.
    regEx.IgnoreCase = True ' Set case insensitivity.
    regEx.Global = True ' Set global applicability.
    Set Matches = regEx.Execute(strng) ' Execute search.
    For Each Match In Matches ' Iterate Matches collection.
    RetStr = RetStr & "Match found at position "
    RetStr = RetStr & Match.FirstIndex & ". Match Value is '"
    RetStr = RetStr & Match.Value & "'." & vbCrLf
    Next
    RegExpTest = RetStr
    End Function</pre>


    The VBScript RegExp object knows nothing of Word ranges and selections, you have to feed the beast with strings, and it will return a "Matches"-Collection.

    If you start the VBScript-editor, you will get (online-)help.

    BTW, Graham Mayor and I just put together some <A target="_blank" HREF=http://www.mvps.org/word/FAQs/General/UsingWildcards.htm>info on Word wildcard searches</A>.


    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>Greetings, Klaus

  9. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Thanks Klaus,

    You are always spilling good ideas all over the place.

    What do you think of my search string for locating an address in a document? Not bad for a novice wildcarder?

    Do While True
    .Execute Findtext:="[,][ ][A-Z]{2}[ ]{1,3}[0-9]{5}"
    If Not .Found Then
    .Execute Findtext:="[,][ ]California[ ]{1,3}[0-9]{5}"
    End If
    If .Found Then...

    Our original poster, David, referred to a problem I had as well: how to utilize an OR in the findtext. I think I got the findtext refined as far as I can go, but still I needed another test.

    For example, my search for the state on the CityStateZip line of an address only looks for 2 characters followed by 1 to 3 spaces, etc. But if the state was spelled out, i.e. "California", the search was useless. Above shows my solution: another execute if NOT FOUND. The problem with this technique is pretty obvious: I've only got 49 more states to test for. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Feel free to enlighten me if there is a better way.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    > Not bad for a novice wildcarder?

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>Not bad at all!! I'm no expert by any definition, but like to play with wildcards a lot. Whether it's easier to use wildcards, the "Like" operator, RegExp, or "traditional" parsing (by characters or words) depends very much on circumstances.
    To find addresses in any file (interspersed in text), I would probably have used about the same approach as yours, but search "candidates" with one wildcard search, and then parse the stuff that is found to check for valid addresses.

    The wildcard search [A-Z][a-z]@, [A-Z][A-Za-z]@[ ^s]{1,3}[0-9]{5} should find likely candidates (containing either "CA" or "California").
    The first word in the found text should be the city, the second the state, and the third the ZIP code.
    The second word (state) probably has to be checked with a very long and boring Select/Case to verify it's a valid state (and perhaps to "normalize" California --> CA ...).
    Starting from that, I'd look at the word to the left, and include it in the city name if it's capitalized (to find "New York", "San Francisco" ...). Looking further to the left might find the street, house number and the name of the addressee.

    I don't know enough about the US postal system to parse the ZIP code (perhaps the code can contain hyphens? or a different length for different states? Perhaps there is a file with city names/ZIP codes to verify the ZIP codes and detect typos?) ... I guess it's a job that is never really finished <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>Regards, Klaus

  11. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Klaus,

    The "cards" [A-Z][A-Za-z]@ do not account for two name states like New Mexico or North Dakota.

    So, adding a space after the final z makes that work:

    [A-Z][A-Za-z ]@

    Also, you use the ^s (a non-wildcard way of searching for a non-breaking space -- correct?)

    Can you combine "special characters" with wildcards? I thought they were exclusive.

    Also, I think we can start the State name search with [A-W] since no states begin with X, Y or Z! Minor point, but what the heck!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  12. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    > Also, you use the ^s (a non-wildcard way of searching for a non-breaking space -- correct?)

    Most special characters do work as usual, apart from footnotes (you have to search for ^002 -- ^f doesn't work) and fields (which can't be found at all). No idea why they don't appear in the list of special characters when you have "Wildcards" selected ... The link I put in my first reply has more on that.

    For the rest: I usually dreamt of Tahiti in geography class <img src=/S/doze.gif border=0 alt=doze width=15 height=15>, so I don't know the names of all the <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> states by heart.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Klaus

  13. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    P.S.

    Should [A-z] adequately substitute for [A-Za-z]?

    If not, why not?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  14. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Klaus,

    I'm going to start a new thread on this topic. I think I've discovered a bug or an anomally that I'd like some feedback on. Take a look please. Thanks.

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=71376&page=0& view=collapsed&sb=5&o=0&fpar>this post</A>
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  15. #14
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    [A-z] also finds the characters that come between the upper and lower case alphabet []^_`

    But it's not always easy to say what characters are in a range. [a-b] will for example also match a lot of "a"'s with accents (

  16. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Find (97 SR2)

    Ok, Thanks. That helps. It'll stick with [A-Za-z].
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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