REGEX patters, boundary v anchor

Question

Hi, I have been trying to test for a piece of text followed by 0 to 2 characters, but no more;

so this;
=REGEXTEST("a,bc","a.{0,2}$",1) = false as expected ; literal "a" followed by 0 to 2 but no more characters
but trying to search within a string, so anchor $ cannot be used , so a word boundary \b ,

       =REGEXTEST("c d e f a,bc z","a.{0,2}\b",1)  = true ?      but it's the same 'a,bc"    

bu t =REGEXTEST("c d e f abcd z","a.{0,2}\b",1) = false "abcd" is not "a" followed by 2 other charcters correct

I know this can get quite complex especially with look aheads etc but can someone explain what is happening or
link to good site on patterns ?

Richard

Answer 1

The pattern a.{0,2}\b means "a" followed by a word break with 0 to 2 characters. Your test string has c d e f a,bc z, so there is a word break directly after the "a". (word break: word character followed by a non-word character or end of string, or word character preceded by a non-word character or beginning of string.(

It is not clear to me what you want to achieve, so it is difficult to suggest an alternative.

Answer 2

\b is a word-boundary anchor, not a “no more characters” limiter, so it behaves very differently from $.

What the patterns are doing:

1. =REGEXTEST("a,bc","a.{0,2}$",1) Pattern: a.{0,2}$
   • a – match the literal a.
   • .{0,2} – match 0 to 2 of any characters.
   • $ – end of string anchor.
   In "a,bc" there are 4 characters after a (",bc"), so .{0,2} cannot consume all remaining characters and still be at the end of the string. Therefore the match fails → FALSE.
2. =REGEXTEST("c d e f a,bc z","a.{0,2}\b",1) Pattern: a.{0,2}\b
   • a – match a in "a,bc".
   • .{0,2} – can match up to 2 characters after a.
   • \b – word boundary: a position between a word character (\w) and a non‑word character (\W), or at start/end of string.
   In the substring "a,bc":
   • a is a word character.
   • , is a non‑word character.
   The engine can match:
   • a (the a), then
   • .{0,2} as zero characters (it is allowed to match 0), so the current position is still between a and ,.
   • That position is a word boundary (a = \w, , = \W), so \b succeeds.
   The full match is just "a", not "a,bc". Because some part of the string matches the pattern, REGEXTEST returns TRUE.
3. =REGEXTEST("c d e f abcd z","a.{0,2}\b",1) Now the substring is "abcd":
   • a and b and c and d are all word characters (\w).
   Try all ways .{0,2} can match:
   • 0 chars: position between a and b → \w/\w → not a boundary.
   • 1 char (b): position between b and c → \w/\w → not a boundary.
   • 2 chars (bc): position between c and d → \w/\w → not a boundary.
   There is no way to end on a word boundary after matching a plus up to 2 chars, so the pattern fails → FALSE.

Why \b is not a replacement for $ here

• $ anchors the match to the end of the string (or line, depending on options).
• \b anchors the match to a word boundary (between \w and \W, or at start/end of string), which can occur anywhere inside the string.
• .{0,2} is allowed to match fewer than 2 characters, so the engine will choose any length that allows \b to succeed.

Pattern to enforce “no more characters after the 0–2”

To require that there are no more characters at all after the 0–2 characters, $ (end‑of‑string) is needed. When searching within a larger string, this means the match must end at the end of the entire cell value, not just at a word boundary.

If the requirement is “a followed by 0–2 characters and then a non‑word character or end of string”, a closer pattern would be:

\ba.{0,2}(?=\W|$)

but note that Excel’s REGEX functions use PCRE2; lookaheads like (?=...) are part of PCRE2, while some environments (like RE2) do not support them.

For background on anchors and word boundaries, see the .NET regex documentation, which uses the same concepts:

• ^, $, \A, \Z, \z, \b, \B as position anchors.
• \b = boundary between \w and \W.

---

References:

• REGEXTEST Function
• Anchors in Regular Expressions
• Word Boundary: \b
• Regular Expression Language - Quick Reference
• Regular Expression 2 (re2.h) syntax