How to use regex with strings containing special characters

Question

Level 1

46 points

How to use regex with strings containing special characters

I'm using XLOOKUP with a regular expression and values that contain an asterisk (*). I can use exact matches, but I want to be able to do partial matches and seem to require a regular expression.

Using a partial match with "exact or next smallest/largest" as the 5th parameter to XLOOKUP produces unwanted results.

In the above, the same formula is in each cell of column D. It uses the first 6 characters of the value in A to search B and, if a match is found, return the adjacent value in C. Otherwise, return "Not found".

At D3, the value in A3 is used in the expression and the asterisk is treated as a wild card, so it matches the row above instead of the row I want it to.

At D5, the asterisk being treated as a special character means it doesn't match the literal asterisk in B4.

One option is to do a search and replace on the value in A so that "*" becomes "\*" (i.e. quote the *) and the asterisk is treated as a literal and not a special character.

This "works", but it means I have to quote any special characters (+, ?, [, ] and so on) so they're treated as literals in the regular expression. I'm dealing with financial transaction data so the character set in the data is pretty limited and at the moment I've only come across asterisks. However I expect other characters may appear in future causing difficult to find bugs.

I could create copies of column A and B with special characters removed, but that seems clumsy.

Is there any other way to tell REGEX to ignore special characters in the regular expression string?

Posted on Feb 14, 2026 7:22 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,341 points

Feb 14, 2026 9:03 PM in response to ozRob

I'm not seeing an easy way to do it with REGEX. I am not finding a way to do a literal match on a string that contains regex metacharacters. But, if by "partial match" you mean "starts with the same 6 characters", with the new array functions that you can do this:

=XLOOKUP(LEFT($A3,6),LEFT(B,6), C,"Not Found",2)

Edit: There are some issues with this if a string in column A is less than 6 characters. That can be overcome if you need it to be. Instead of 6 use MIN(6,LEN($A3)) in both places.

Reply

Answer 2

Badunit

Level 7

31,341 points

Feb 15, 2026 5:51 AM in response to ozRob

ozRob wrote:

Thank you, I can't understand why I didn't think to trim both strings and do a simple comparison. It makes perfect sense.

Prior to version 14.4 you couldn't do it this way, you would have had to create a new column to turn B into 6-character strings and you would use that column in the formula. The new array capabilities let it be done within a function. LEFT(B,6) creates an internal array of column B with each row truncated to 6 characters.

I'm still pondering how to do the more general case of partial matching where it could be found anywhere in the string, like using XLOOKUP of "foo*" to find "Let foo* be the way".

Reply