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
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
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"")"
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 characterBasically, 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
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
Having searched a bit deeper it appears that you can use "?", "*", and "#" wildcards in this context, but not ranges like [A-Z].
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
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |