0

When I ctrl-f in a spreadsheet, I can find the text, "child Support," but when I'm using it as a pattern it fails. Other obvious words work, like "Date," for instance.

This spreadsheet was converted from a PDF. Could there be some hidden null characters that are causing this column to be ignored?

Private Function RegExTest(s As String) As String
    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(child support)"
    re.Global = True

    For Each match In re.Execute(s)
        MsgBox match.value
        RegExDate = match.value
        Exit For
    Next
    Set re = Nothing
End Function
niton
  • 8,771
  • 21
  • 32
  • 52
Jeff Young
  • 140
  • 2
  • 10
  • 1
    Regexes are case-sensitive by default - could that be the problem? – Tim Pietzcker Feb 28 '15 at 16:07
  • Also, consider `"(child\s+support)"` as a sanity check. The whitespace may not be what you think it is. – J0e3gan Feb 28 '15 at 16:09
  • I have the right case, and I just tried the \s.... No luck on either. It's so weird -- literally the cell above it is "Deductions," and when I put that in, it can be found! All the cell formats are General, too. – Jeff Young Feb 28 '15 at 16:11
  • So if you copy and paste the contents of the cell in question into your VBA string (i.e. rather than typing what you see), it still does not match? – J0e3gan Feb 28 '15 at 16:14
  • 1
    Ok -- no luck. I also pasted into notepad++. Don't see any obvious hidden characters. – Jeff Young Feb 28 '15 at 16:28
  • 1
    Ok -- duh -- sorry to all the Excel power users in the world... The cell is locked. I'll figure out in the code to unlock it. Thanks for all the suggestions. – Jeff Young Feb 28 '15 at 16:32
  • Answer your own question, and accept your own answer. Unless this is address in another question & answer, this may be helpful to someone else. – J0e3gan Feb 28 '15 at 16:33
  • Still hacking -- the Locked cells are the issue -- but unlocking them doesn't resolve the issue. It's like there's a memory of the property or another issue. – Jeff Young Feb 28 '15 at 16:55
  • Jeff - I don't know if this is a typo but your function is declared as `RegExTest` and you are stuffing the date into `RegExDate`. –  Feb 28 '15 at 17:26
  • Ya, this isn't the code I'm using. Right now, I'm staring at XML trying to figure out what's different between the 'good' and 'bad' cells. – Jeff Young Feb 28 '15 at 18:17
  • Add `.ignorecase = true` to your declarations. Also try `"(child[\s\xA0]+support)"` for your code to look for `NBSP`. – Ron Rosenfeld Feb 28 '15 at 20:57

2 Answers2

0

Remember that:

"child Support"

is not the same as:

"child support"
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

As answered by Gary's student, regex is case-sensitive. You had fallen in one late binding drawbacks which is the lack of intellisense.

If you had used early binding (Referenced MS VBscript regular expression 5.5 class from your IDE Tools menu > References), you would have seen the regex object had a case sensitive property that be set to true or false thanks to VBA intellisense:

enter image description here

Amen Jlili
  • 1,884
  • 4
  • 28
  • 51
  • Thanks -- that's not the issue... I know this: re.Pattern = "(child support)" is causing a lot of people on the Asbperger's Spectrum a lot of problems because it's in lower case, but that's not the problem. Thanks. PDFs and Excel is where data goes to die, not manipulated. – Jeff Young Mar 01 '15 at 19:19
  • @JeffYoung Can you upload the dysfunctional, converted PDF to a public site and post a link here? – Ron Rosenfeld Mar 01 '15 at 21:29
  • Sorry, I just had to give up... We had to move on. I really hate VBA, Excel -- it seems too inconsistent. – Jeff Young Mar 04 '15 at 20:46