Solved [VBA] Add Wildcard for uppercase alpha char to FIND formula?

Tags:
Microsoft Excel 2010 - complete product...
February 26, 2018 at 11:53:43
Specs: Windows 10 Enterprise
Is it possible to add a wildcard to the sample formula to require an uppercase alpha character follows
the specified search word (i.e. ‘PEAR’ + any uppercase alpha character immediately following the
‘PEAR’ characters.

I have several filter formulas in an existing macro that have extensive filter criteria so I don’t want to
change to regex or other VBA formulas and don’t want to add any additional lines of code, I just want to
edit the existing formula to allow for this requirement.

I could obviously list all 26 variants (PEARA, PEARB, PEARC, etc.) but I’m wondering if there is an easier
way to accomplish this.

Please let me know if there is a way to modify the existing code to also add an “X” for PEAR
[plus any uppercase alpha character].

.Range("BA2").FormulaR1C1 = _
 "=IF(ISERROR(FIND(""APPLE"",RC8)),IF(ISERROR(FIND(""BANANA"",RC8)),"""",""X""),""X"")"

Please let me know if there is a way to modify the existing code to also add an “X” for PEAR[plus any
uppercase alpha character].

Thank you!

Sample code: 

 Sub TestWildcard()
 
    Dim wb As Workbook
    Dim ws As Worksheet
    
    
    'Need to add variant for "PEAR" + a wild card to for any uppercase alpha character (A-Z)

    'Note:  "PEAR" must be uppercase, must be immediately followed by an uppercase alpha character, doesn't  matter if PEAR is preceded by any text ... as long as it is immediately followed by uppercase alpha letter
    'Examples of matches:  PEARA, PEARB, PEARC, PEARCplusAnyAddlCharacters, abcPEARA , sPEARABCD432, etc
    'Examples of no match: pearA, peara, PearA, PEAR A,PEARa, PEAR, PEAR2A

   With ActiveSheet
             .Range("BA2").FormulaR1C1 = "=IF(ISERROR(FIND(""APPLE"",RC8)),IF(ISERROR(FIND(""BANANA"",RC8)),"""",""X""),""X"")"
   End With
    
End Sub


See More: [VBA] Add Wildcard for uppercase alpha char to FIND formula?

Report •

✔ Best Answer
February 27, 2018 at 04:06:25
ijack,

Can you supply a link or a formula example that shows how to use wildcards with the Excel FIND function? The OP is not using the Excel Find feature in this instance, the OP is using the FIND function in a formula.

All of my research and understanding matches the following, which can be found on many websites related to the FIND function:

"FIND and FINDB are case sensitive and don't allow wildcard characters."

That statement can be found here:

https://support.office.com/en-us/ar...

If you have found a way to use wildcards with the FIND function, please point us to a reference or post a working example. It would be really helpful to see how it can be done.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


See in Thread ↓

#1
February 26, 2018 at 12:09:08
Try "PEAR[A-Z]"

https://msdn.microsoft.com/en-us/li...


Report •

#2
February 26, 2018 at 13:14:42
Thanks for the suggestion ijack but when I modfied the string as suggested, it did not work. 

             .Range("BA2").FormulaR1C1 = "=IF(ISERROR(FIND(""APPLE"",RC8)),IF(ISERROR(FIND(""BANANA"",RC8)),IF(ISERROR(FIND(""PEAR[A-Z]"",RC8)),"""",""X""),""X""),""X"")"


Report •

#3
February 26, 2018 at 13:21:04
I'll let you figure out how to incorporate this into your formula. My guess is that you are not really searching for fruit, so I'll just toss out the concept and let you modify it for your actual needs.

The following formula will return 1 of 3 results:

1 - Just PEAR - if H2 contains PEAR with no characters following it
2 - Uppercase Alpha - if H2 contains PEAR followed by an Uppercase Alpha character
3 - Not Uppercase Alpha - if H2 contains PEAR followed by a character that is not an Uppercase Alpha character

Basically, it looks at the ASCII Code of the character following PEAR (if one exists) and determines if it is between 65 and 90, inclusive.

=IF(AND(FIND("PEAR",H2)>0,LEN(H2)>4),
IF(AND(CODE(MID(H2,5,1))>64,CODE(MID(H2,5,1))<91),
"Uppercase Alpha","Not Uppercase Alpha"),"Just PEAR")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions
See More ↓

#4
February 26, 2018 at 13:43:25
re: Try "PEAR[A-Z]"

Unless I'm mistaken, you can't use wildcards with the FIND function with Excel.

Since User444 wants to use VBA to place a formula containing the FIND function directly in a cell, the option of using wildcards does not appear to be available. [A-Z] would probably work with the Like function in VBA but User444 specifically stated (for some reason):

"I ... don’t want to add any additional lines of code, I just want to edit the existing formula to allow for this requirement."

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
February 27, 2018 at 00:50:11
Having searched a bit deeper it appears that you can use "?", "*", and "#" wildcards in this context, but not ranges like [A-Z].

Report •

#6
February 27, 2018 at 04:06:25
✔ Best Answer
ijack,

Can you supply a link or a formula example that shows how to use wildcards with the Excel FIND function? The OP is not using the Excel Find feature in this instance, the OP is using the FIND function in a formula.

All of my research and understanding matches the following, which can be found on many websites related to the FIND function:

"FIND and FINDB are case sensitive and don't allow wildcard characters."

That statement can be found here:

https://support.office.com/en-us/ar...

If you have found a way to use wildcards with the FIND function, please point us to a reference or post a working example. It would be really helpful to see how it can be done.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •
Start a discussion

Ask Question