Function to find a portion of text with a 'wildcard' in a cell

[Nathan Asius]

Previously this board solved my problem of building a formula for me that searches for a portion of a text nesting the arguments : IF(ISNUMBER(SEARCH..

the formula was this:
=IF(AND(ISNUMBER(SEARCH("44W",A1)),ISNUMBER(SEARCH("Willow",A2))),WillowSpecies, IF(AND(ISNUMBER(SEARCH("22F",A3)),ISNUMBER(SEARCH("Maple",A4))),MapleSpecies, IF(AND(ISNUMBER(SEARCH("6 Bark",A5)),ISNUMBER(SEARCH("Elm",A6))),ElmSpecies, IF(A7="Books","Bookcase",""))))

My sheet has evolved and I now need a tweak to include a wildcard in one of the search terms.
Instead of specifying the numbers 44W in the first arguments, or 22F in the second arguments etc.. I'm hoping there's a way to make a wildcard out of that search term so that it would now look something like 4_W or 4#W, meaning it would look for any number between the 4 and the W, or respectively the 2 and the F, but only allowing numbers. It would need to exclude letters.

Can this formula (not VBA) be expanded to have this wildcard type function?

 

[StephenCrump]

Could you use something like this? (I've assumed you will allow only one character between "2" and "W")

	A	B
1	Blah29wblah	TRUE
2	2 w	FALSE
3	21W	TRUE
4	211w	FALSE
5	Blah2Aw blah	FALSE
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=LET(n,SEARCH("2?w",A1),ISNUMBER(--MID(A1,n+1,1)))

 

[Nathan Asius]

You're correct that it only needs to search for the one character between the "2 and W".
So you're solution may work but I'm not getting the syntax down correctly in the nested formula I need to use.

[ClrSeries1 is a named range]
My Actual operating formula should be:
=IF(AND(LET(n,SEARCH("2?w",CLrSeries1),ISNUMBER(--MID(ClrSeries1,n+1,1))),("Soffit",$E14)),INDEX(TblSoffit[[29g]:[26g]],MATCH((QuoteBoard!$G14,TblSoffit[Soffit]))))

The two arguments for the AND are:
1: (LET(n,SEARCH("2?w", ClrSeries1),ISNUMBER(--MID(ClrSeries1,n+1,1)
2: ("Soffit",$E14)
Then there's the Index-Match formula which searches for prices in an Table and functions fine on it's own.
3. INDEX(TblSoffit[[29g]:[26g]],MATCH((QuoteBoard!$G14,TblSoffit[Soffit])

So my trial and error in placing any number of parentheses after the AND, and to close the formula haven't been working. Am I missing something?

 

[Nathan Asius]

Maybe this may make it easier.
When searching for that wildcard in the named range ClrSeries1, it needs to find only 1 character among a string of three. Strings will never be more than 3. and the position of the wildcard will always be the 2nd (in the middle).

 

[StephenCrump]

Presumably the second AND condition should be $E14="Soffit", rather than ("Soffit",$E14).

The problem is with the AND(), which will collapse the result to a single TRUE/FALSE. It will be TRUE only if every CLSeries1 value has the form "2?w"

I suspect you need a construction like this: AND(OR(test1),test2). But it's a little hard to say what formula will work for you without seeing your layout, and the desired results.

 

[StephenCrump]

When searching for that wildcard in the named range ClrSeries1, it needs to find only 1 character among a string of three. Strings will never be more than 3. and the position of the wildcard will always be the 2nd (in the middle).

In that case, you could test more directly, along these lines:

=ISNUMBER(MATCH(A1,"2"&SEQUENCE(10,,0)&"w",))

 

[Nathan Asius]

In that case, you could test more directly, along these lines:

=ISNUMBER(MATCH(A1,"2"&SEQUENCE(10,,0)&"w",))

Yes. I've seen my formula result in a True/False.
So I tried this formula here and it worked.
Thanks Stephen