HotXLS evaluates the Microsoft 365 regular expression functions REGEXTEST, REGEXEXTRACT and REGEXREPLACE inside its own Delphi formula engine, with no Excel installation and no system regex DLL. The HotXLS calculator links a pinned 16-bit PCRE2 10.49 build statically, matches UTF-16 text directly, broadcasts over arrays and charges every backtracking step against the formula's resource budget
That last part is the reason this article exists. A workbook that arrives from a customer is untrusted input, and a regular expression inside a formula is code the customer wrote. Pattern plus text is all it takes to pin a CPU core for minutes with catastrophic backtracking, the classic ReDoS shape (a+)+$ against a long run of a characters with a trailing b. A server that recalculates uploaded workbooks cannot afford that, so the interesting work in HotXLS 2.384.112 was not calling PCRE2. It was making PCRE2 stoppable, accountable and identical on every target
What do REGEXTEST, REGEXEXTRACT and REGEXREPLACE return in HotXLS?
The HotXLS formula engine follows Microsoft's published signatures, and the argument semantics are the ones you already know from Excel. Matching is case-sensitive by default. The final case_sensitivity argument takes 0 for case-sensitive and 1 for case-insensitive, and any other value returns #VALUE!. Inline PCRE2 options such as (?i) or (*UCP) also work when you would rather keep the behaviour inside the pattern
REGEXTEST(text, pattern, [case_sensitivity]) returns a Boolean, and a failed match is simply FALSE, never an error. REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) has three modes: 0 returns the first complete match, 1 returns every complete match as a vertical array, and 2 returns the capture groups of the first match as a horizontal array. No match returns #N/A, mode 2 on a pattern without capture groups returns #VALUE!, and an optional group that did not participate yields an empty string rather than an error
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) uses PCRE2 replacement syntax, so $1 and ${name} reference captures. An occurrence of 0 replaces every match, a positive value picks the nth match from the start and a negative value counts from the end. When the selected occurrence does not exist, HotXLS returns the original text unchanged. Numeric selectors are truncated towards zero, the same way Excel treats fractional integer arguments
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Orders');
Sheet.Cells[2, 1].Value := 'PO-2026-00417 shipped to Graz';
// TRUE when the cell holds a purchase order number
Sheet.Cells[2, 2].Formula := 'REGEXTEST(A2, "PO-\d{4}-\d{5}")';
// First complete match: "PO-2026-00417"
Sheet.Cells[2, 3].Formula := 'REGEXEXTRACT(A2, "PO-\d{4}-\d{5}")';
// Swap year and sequence with numbered captures: "PO-00417-2026 ..."
Sheet.Cells[2, 4].Formula :=
'REGEXREPLACE(A2, "PO-(\d{4})-(\d{5})", "PO-$2-$1")';
// Case-insensitive test with the final argument
Sheet.Cells[2, 5].Formula := 'REGEXTEST(A2, "graz", 1)';
Book.Recalculate;
Book.SaveAs('orders.xlsx');
finally
Book.Free;
end;
end;Formula text in Delphi is a plain string literal, so \d needs no escaping on the Pascal side. Only the Excel double quotes inside the formula matter, and they follow normal Excel rules. On save, the XLSX writer stores these names with the _xlfn. future-function prefix that Excel expects, and a reopened workbook recalculates them again. The classic BIFF8 engine can evaluate the functions in a contextual call, but it cannot write them as native .xls formula tokens, so keep regex formulas in XLSX output
How does array broadcasting work with regex formulas?
REGEXTEST, REGEXREPLACE and mode 0 of REGEXEXTRACT broadcast across compatible operands, which is what makes them useful on real columns. Each argument may be a scalar, a row, a column or a rectangle, and HotXLS takes the largest row and column count as the result shape. An operand whose dimension is neither 1 nor that maximum makes the whole call return #VALUE!, the usual Excel array mismatch. So REGEXTEST(A2:A500, "^[A-Z]{2}\d{9}$") yields 499 Booleans, and a column of patterns against a single text cell works the same way in the other direction
Modes 1 and 2 of REGEXEXTRACT refuse to broadcast. Each element would produce a vector of a different length, and a ragged set of vectors cannot occupy a rectangular formula result. Rather than truncate or pad silently, HotXLS reports the call as unsupported. If you need all matches from many cells, iterate the cells and extract one at a time, or reshape the problem with LET and LAMBDA closures in the HotXLS formula engine so that each closure call sees one text value
Error placement inside a broadcast result is deliberate. An Excel error for one element, say #N/A from an extraction that found nothing in row 37, lands in that one cell of the result while the other cells keep their values. A resource limit or a cancellation is different: it stops the entire call and never publishes a partially filled array, because half a result that looks complete is worse than an explicit failure
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Opts: TXLSFormulaEvaluationOptions;
Eval: TXLSFormulaEvaluationResult;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Log');
Sheet.Cells[1, 1].Value := 'GET /api/v2/orders 200 18ms';
Opts := XLSDefaultFormulaEvaluationOptions;
Opts.MaxEvaluationSteps := 200000; // cap the work this formula may do
// Mode 2: capture groups of the first match as a 1 x 3 array
Eval := Sheet.EvaluateFormulaAt(1, 2,
'REGEXEXTRACT(A1, "(GET|POST) (\S+) (\d{3})", 2)', xlfrsA1, Opts);
case Eval.Status of
xlfeOk:
if VarIsArray(Eval.Value) then
for I := VarArrayLowBound(Eval.Value, 2) to
VarArrayHighBound(Eval.Value, 2) do
Log(VarToStr(Eval.Value[0, I]));
xlfeResourceLimit:
Log('Pattern exceeded its budget: ' + Eval.Issue.Message);
else
Log('Evaluation failed: ' + Eval.Issue.Message);
end;
finally
Book.Free;
end;
end;TXLSXWorksheet.EvaluateFormulaAt is the right entry point when the pattern comes from somewhere you do not control. It evaluates the formula as if it lived at the given row and column, returns the full array instead of a single cached cell value, and takes TXLSFormulaEvaluationOptions with explicit step, array-memory and cancellation controls. The HotXLS formula engine and custom function guide covers the rest of that evaluation API
How does HotXLS stop a catastrophic backtracking pattern?
HotXLS stops runaway patterns by making PCRE2 report back to the formula context on every step. The backend compiles every pattern with PCRE2_AUTO_CALLOUT, which makes PCRE2 call a Pascal callback at each pattern item during matching. That callback charges one unit of work to the active evaluation state, checks the cancellation flag and checks resident memory. When any of those says stop, the callback returns PCRE2's callout-abort code and the match unwinds cleanly inside PCRE2. The formula surfaces as xlfeResourceLimit or a cancellation status, not as a hung thread
Memory is metered the same way. Every PCRE2 context is created with a per-instance general context whose allocator is a Pascal function, so the compiled pattern, the match data and PCRE2's internal backtracking frames are all allocated through HotXLS. Each block carries a small header and is aligned to 16 bytes, and its size is added to a running total that the budget check compares against the formula's array-memory limit. Output buffers, extraction vectors and the replacement string are added to the same total, which means a pattern that matches millions of tiny strings hits the ceiling just as surely as one that backtracks forever
The budget callback also runs under a guard that matters more than it looks. A Delphi exception raised inside a callback that PCRE2 called must never propagate upward, because between the callback and your code sit C stack frames compiled without any knowledge of Delphi exception tables. Unwinding through them is undefined at best and a corrupted stack at worst. The HotXLS bridge catches every exception in the callback, latches the instance into an aborted state and returns an ordinary error code to PCRE2. Your code sees a failed formula, never an exception that crossed a foreign frame
The cost is real and worth stating. Auto callouts make matching slower than a bare PCRE2 interpreter, and HotXLS compiles the pattern once per evaluated element, so a broadcast over 50,000 cells pays 50,000 compilations. For bulk text work on trusted data, a dedicated Delphi regex pass outside the workbook will be faster. Inside a workbook that strangers send you, bounded and predictable beats fast
Why a static UTF-16 PCRE2 build with no JIT?
Excel strings are UTF-16, and so are Delphi WideString values, which made the 16-bit PCRE2 library the only sensible choice. HotXLS hands formula text to pcre2_match_16 with PCRE2_UTF set, and every offset that comes back is a UTF-16 code unit index. No UTF-8 round trip happens, so nothing can drift on surrogate pairs. Where an empty match forces the scan to advance, HotXLS follows PCRE2's global-match rule: retry a nonempty anchored match at the same position, then step one Unicode scalar value, which moves two code units past a surrogate pair instead of splitting it
JIT is off on purpose. The JIT compiler needs executable memory and its own JIT stack, which pulls in platform-specific code and allocations outside the per-formula allocator. The interpreter keeps every byte under the HotXLS budget, keeps every match under the callout and behaves the same on all five targets. Each TXLSRegex instance, the internal class behind these formulas, owns its own contexts, so parallel calculation workers share no mutable PCRE2 state
The linking story is where the build got unusual. Delphi links C object files with {$L}, and its linker resolves symbols between Pascal units and C objects well enough, but it does not resolve arbitrary circular references between separate C object files. PCRE2 is two dozen translation units that call each other in every direction. The fix is a unity build: all of PCRE2 is compiled as one translation unit into one pcre2_unity object per target, so every internal reference resolves inside a single object and the Delphi linker only sees the exported _16 functions
The C runtime was the second problem. PCRE2 calls malloc, memcpy, memcmp and friends, and other C backends linked into the same executable may already bring their own copies of those names. HotXLS preprocesses PCRE2 so those calls go to private names such as hotxls_regex_memcpy and implements them in Pascal. Clang can still synthesize plain memset and memchr calls after preprocessing, so those two exist as well, inside the same unit. Symbol decoration has to match each object format: the Win32 OMF object expects the leading-underscore C names, while the Pascal declarations for the Win64 ELF object, the Win64 COFF object and the FPC builds bind the bare names
Stack probes were the subtle one. A C function with a frame larger than a page calls a probe routine first, which touches each 4 KB page in order so that Windows can commit the stack through its guard page. A stub that simply returns links fine and works in every unit test with a small pattern, then crashes the first time a deep frame jumps past the guard page. HotXLS implements real probes in assembly for each Windows object format: __chkstk_noalloc for the Win32 object, __chkstk for the Win64 ELF-format object and ___chkstk_ms for the Win64 COFF object. On Linux x64 and macOS ARM64, FPC links the system C library instead, and every other FPC target reports the backend as unavailable rather than quietly falling back to a different regex dialect
Where are the boundaries?
The regex functions run on Delphi and C++Builder Windows builds and on FPC Linux x64 and macOS ARM64. On any other target the formula reports an unsupported function through contextual evaluation instead of producing different answers with another engine. That is the right failure: a workbook that computes differently on a different platform is a bug nobody finds until an audit
Excel equivalence has a stated limit too. The signatures and documented examples follow Microsoft's REGEXTEST, REGEXEXTRACT and REGEXREPLACE pages, but the Excel build used for local validation does not expose these Microsoft 365 functions yet. Edge cases therefore follow the explicit PCRE2 and array contracts described above rather than a claim of matching every Excel build bit for bit. If your workbooks depend on an obscure corner, such as a specific backreference behaviour or a Unicode property class, test that corner directly
FAQ
Does HotXLS need Excel or a regex DLL to calculate REGEXTEST?
No. HotXLS links a static UTF-16 PCRE2 object into your executable and evaluates REGEXTEST, REGEXEXTRACT and REGEXREPLACE in its own Delphi formula engine, with no Excel, COM or external DLL
Can a malicious pattern hang my server?
Not past the configured budget. Every matching step goes through a callout that charges the formula context, so a ReDoS pattern ends with xlfeResourceLimit or a cancellation status and no partial result
Why does REGEXEXTRACT mode 1 fail on a range argument?
Each cell could return a different number of matches, and those ragged vectors cannot form a rectangular result. HotXLS reports the call as unsupported instead of padding or truncating, so extract one cell at a time
HotXLS ships the regex functions alongside LAMBDA, GROUPBY, PIVOTBY and the rest of its calculation engine for Delphi, C++Builder and Lazarus. Details, editions and the trial download are on the HotXLS Delphi spreadsheet component page