In numbers use Match without wildcards

Question

Level 1

19 points

In numbers use Match without wildcards

According to the Numbers help for the Match function, I have to prefix * with tilde if I want to find literally ‘*. and not use * as a a wildcard.

I have thousands of lookups where there is a table of data that includes, numbers, words, characters, punctuation and is likely to contain * and ? on their own or in combination with other characters.

I have hundreds of match functions that look for a seemingly random inputs to see if the input is the large table.

I cannot simply use Match(“~” & input, …) because while blindly prefixing everything with ~ does negate wildcards it breaks all other input. I could try if() within Match to conditionally add ~ in cases where the input is * or ?, such as MATCH( IF(OR(input=“*”,input=“?”) ,”~” ,””) & input, …) but this feels both cumbersome and complex when spread across so many Match functions and in the context of my sheet’s layout.

Is there a better way? Perhaps XMatch and Regex? (I am not familiar with Regex syntax).

Thanks.

MacBook Pro 17″, macOS 10.13

Posted on Jul 16, 2024 8:52 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,341 points

Jul 16, 2024 10:08 AM in response to IHadaName

XMATCH when set to "find exact" does not use wildcards so it is likely to be your best choice. Nothing special is required. Note that the results may not be the same as with MATCH if you are using whole column references like Table 1::B for the search range. MATCH includes the headers in the search. XMATCH does not.

If you still want to use MATCH, you can use REGEX to precede any * or ? in your search string with ~ like in this formula:

SUBSTITUTE(C2,REGEX("(?=[\*\?])"),"~")

The REGEX is saying to "look ahead for either a * or ? and if you see one put a ~ in front of it"

Use the SUBSTITUTE formula in place of your search string

This next version apparently works also (same as before but without the backslashes). The characters * and ? appear to be treated as actuals when inside square brackets.

SUBSTITUTE(C2,REGEX("(?=[*?])"),"~")

Reply

In numbers use Match without wildcards

Similar questions