REGEX: Breaking the Limits with Variable-Width Lookbehind

In my previous article, we explored how (*MARK) allows for context-aware replacements in Excel's REGEXREPLACE(). But context is just as critical when we are extracting data based on historical patterns within a string.

I have been made aware of a fascinating Excel challenge #441 by Omid Motamedi on LinkedIn by fellow Regex enthusiast Hagia Sofia. The idea is to match and split a string at the exact moment a character is encountered that has already appeared before.

Take this example:
To solve this, we want REGEXEXTRACT to return four separate matches from our input string: FG1, 1, G, and F.

To achieve this in a single formula, the regex engine needs to look "back in time" at a variable distance to check if a character has a twin earlier in the string. Traditionally, this was impossible in spreadsheets. But with PCRE2 now natively in Excel 365, we have access to a powerhouse feature: variable-width lookbehind.

However, there is a strict rule we must follow to avoid the dreaded #VALUE! error: the lookbehind must have a determinable maximum length of 255 characters. Let's see how this constraint actually helps us crack the challenge.

The Infinite Trap

To find a duplicate, your first instinct might be to capture a character, look ahead, and then use an infinite quantifier like * (zero or more) inside a lookbehind to see if it appeared earlier:

(?<=\1.*) (Look behind to see if group 1 matches followed by any number of characters)

If you try to wrap this into an assertive lookahead split in Excel: =REGEXEXTRACT(A1,".+?(?=(.)(?<=\1.*)|$)",1)

Boom. #VALUE!

Why? Because .* can theoretically match an infinite number of characters. Excel’s PCRE2 engine can't pre-calculate the maximum width during compilation. To prevent performance crashes, the engine refuses to compile infinite lookbehinds and fails immediately.

The Solution: Bounded Variable Lookbehinds

To make variable-width lookbehinds work in Excel, we simply need to reassure the compiler by setting a hard upper limit using {min,max} quantifiers. Since the absolute compilation ceiling for a lookbehind in PCRE2 is 255 characters, and our backreference \1 occupies 1 character, our maximum remaining budget is exactly 254.

By changing .* to .{1,254}, the engine compiles perfectly. The total width of the combination of a single character in the 1st capture group and a variable width of 1-254 characters after it makes a total of 255 characters. Just within the limits and enough for 99% of real world applications:

=REGEXEXTRACT(A1,".+?(?=(.)(?<=\1.{1,254})|$)",1)

To be more precise: As long as the total combined maximum length inside the variable-width lookbehind does not exceed 255 characters, the engine will compile. In that sense, you aren't limited to single characters; you could even match entire variable-length words to test against duplication.

Step-by-Step

When you run this with the third argument set to 1 (to spill all matches), Excel scans the string lazily with .+? and evaluates the lookahead condition at each step:

1. Match 1 (FG1): The engine matches F, G, 1. It looks ahead and captures the next character: 1. The lookbehind (?<=\1.{1,254}) immediately checks if 1 appeared 1 to 254 characters ago. It did (right next to it!). The condition is met, and the first match stops.

2. Match 2 (1): Starting at the second 1, it looks ahead at G. Has G occurred before? Yes, two steps back. The lookbehind validates it, ending the second match.

3. Match 3 (G): It looks ahead at F. Has F occurred before? Yes, at the very start of the string. Validated.

4. Match 4 (F): The lookahead hits the end of the string ($), completing the final extraction.

Variable-width lookbehind is a game-changer for data cleansing in Excel 365, provided you know how to set the boundaries.

This is part of a series of tips and tricks regarding regular expressions in Microsoft Excel 365. Finding this interesting? Let me know what you would like to read about on this topic!