Wildcard use with SEARCH

unsolved

If I want to find both ATT and AT&T, what kind of wildcard could I use? I can’t use AT*T as that pulls in other words like Atlantic and I can’t use AT?T as that removes ATT from matching.

For further context, I have a SEARCH formula that pulls from a cell where the user can type in text to search for a match. I want to make sure they can match both ATT and AT&T if they use the correct wildcard when searching.

Edit: Realized I didn't really give enough context. Below is a simplified mock-up of what's going on to provide better context. The Search is used to mark the row as True which lets me filter for ZIP codes dynamically for only the company the user is asking for.

In the screenshot below, all the ATT/AT&T rows need to come back as TRUE. But I'm not sure if it's possible to do that with a single input from the user. For 97035, it should come back with the address 5 Oak Ave 97535 because AT&T North should register as TRUE for a Search of ATT.

[Image]

in excelby lurkertheshirker

100%

AutoModerator

/u/lurkertheshirker - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

finickyone

1644

I don’t think you are trying to include a wildcard as such, as you’re not going to accept something returned because SEARCH found “AT£T”.

An answer does depend on exactly what you’re trying to get from your data, but broadly speaking I would have something like this:

=OR(ISNUMBER(SEARCH({"ATT","AT&T"},cell)))

Or perhaps just

=SEARCH("AT&T",SUBSTITUTE(cell,"ATT","AT&T"))

lurkertheshirker

Thanks for taking the time to answer.

My problem is ATT or AT&T is one of hundreds of possible inputs by the user. So what goes inside the Search function is dynamically pulled from a cell that the user updates. Wasn't sure if there was a way they could do a wildcard search to make sure they grab both spelling variations.

I updated the OP to create a mini mock-up of the scenario. Perhaps I'm going about this the wrong way and someone could offer a better solution to what I'm trying to do.

finickyone

1644

I appreciate you’ve got a data validation issues, at what appears to be both the query end and the reference end. A bit like wanting all records returned containing “Joe” or “Joseph” in a field, whether the variable supplied for searching that field is either of those, or potentially something else of some degree of similarity.

Fighting past inconsistent data is always a bit complicated in this regard. Nonetheless I think your only viable option might be to try and define lists of similar/alternative strings.

You can set up to iterate through the addition of single wildcards at each possible point in the input string, so that you will end up searching for all of permutations of a ? wild card (and no wildcard). Ie you could have D2 be:

=LEFT(REPLACE(B2,SEQUENCE(,4),1,"?"),LEN(B2))

Where B2 contains “ATT”, that would prompt D2 to spill (right to G2):

?TT A?T AT? ATT

Then swap the SEARCH against @Name for:

=ISNUMBER(AGGREGATE(15,6,SEARCH(D$2#,@Name),1))

Which looks for each of those adapted search strings in the Name field for the row, reports the earliest (leftmost) hit, and then reports whether a number was generated (for True=found).

You have to beware though that at this point, a record where Name contains “art” or “match” is going to be reported as applicable. This concept opens up a lot of risk of false positives, and you’d be wise to caveat those risks in your output (CYA), especially if this stems from not sanitising the ref data or governing the inputs.

There may be hundreds of inputs that could be put forward for query, but no database can really overcome much data inconsistency. So again, given you know of this particular variance (ATT vs A&TT), I’d probably just define that.

A final approach is to try to sanitise the Name data and query input. If you use this:

=LET(s,MID(@Name,sequence(4e4),1),c,CODE(LOWER(s)),CONCAT(IF(OR(c=32,ABS(c-109.5)<13),s,"")))

It should strip out all but letters and spaces from that string. Do the same to the query and you should have normalised data to compare (no “&” persisting on either side).

lurkertheshirker

Thanks for the suggestions. I think your second option is the safer bet. While there are a couple thousand entries, there’s probably only a handful of companies where I would need to standardize the name, so cleaning both sides as you said will probably be for the best.

I’ll take a look at your solution to strip out numbers and symbols and give it a try tomorrow. Thanks.

Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ABS	Returns the absolute value of a number
AGGREGATE	Returns an aggregate in a list or database
CODE	Returns a numeric code for the first character in a text string
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
IF	Specifies a logical test to perform
ISNUMBER	Returns TRUE if the value is a number
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
LOWER	Converts text to lowercase
MID	Returns a specific number of characters from a text string starting at the position you specify
OR	Returns TRUE if any argument is TRUE
REPLACE	Replaces characters within text
SEARCH	Finds one text value within another (not case-sensitive)
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUBSTITUTE	Substitutes new text for old text in a text string

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.}
^{[Thread #34862 for this sub, first seen 27th Jun 2024, 23:08]} ^[FAQ] ^{[Full list]} ^[Contact] ^{[Source code]}