REGEX Function (LibreOffice Calc)

• Extracts text matching a pattern
• Replaces text matching a pattern
• Validates text against a pattern
• Supports capturing groups
• Supports backreferences in replacements
• Works with text, numbers (converted to text), and formulas

It is designed to be powerful, expressive, and ideal for advanced text manipulation.

REGEX(text; pattern; replacement; [flags])

Arguments

• text:
  The text to process.

• pattern:
  A regular expression defining what to match.

• replacement:
  The replacement text.

  • Use empty string "" for extraction mode
  • Use $1, $2, etc. for captured groups

• flags: (optional)

  • “g” → global (replace all matches)
  • “i” → case-insensitive
  • “m” → multi-line
  • Combine flags as needed (e.g., “gi”)

1. Extraction Mode

Use replacement = "" to return the matched text.

2. Replacement Mode

Provide replacement text to transform the input.

3. Validation Mode

Use patterns like ^...$ to test full-string matches.

Extract digits

=REGEX("A123B"; "\d+"; "")

Returns "123".

Extract first word

=REGEX(A1; "^\w+"; "")

Replace digits with X

=REGEX("A123B"; "\d+"; "X")

Returns "AXB".

Remove all non-digits

=REGEX(A1; "\D+"; ""; "g")

Case-insensitive replacement

=REGEX("Hello WORLD"; "world"; "Earth"; "i")

Extract email username

=REGEX(A1; "^[^@]+"; "")

Extract email domain

=REGEX(A1; "@(.+)$"; "$1")

Extract file extension

=REGEX(A1; "\.([^.]+)$"; "$1")

Extract everything before extension

=REGEX(A1; "\.[^.]+$"; "")

Extract numbers from mixed text

=REGEX(A1; "\d+"; "")

Extract the nth number using capturing groups

=REGEX(A1; "(?:\D*\d+){" & B1 & "}"; "")

Normalize whitespace

=REGEX(A1; "\s+"; " "; "g")

Remove HTML tags

=REGEX(A1; "<[^>]+>"; ""; "g")

Extract quoted text

=REGEX(A1; "\"([^\"]*)\""; "$1")

Extract IPv4 octets

=REGEX(A1; "(\d+)\.(\d+)\.(\d+)\.(\d+)"; "$1|$2|$3|$4")

Validate a phone number

=REGEX(A1; "^\d{3}-\d{3}-\d{4}$"; "valid"; "i")

Validate an email

=REGEX(A1; "^[^@]+@[^@]+\.[^@]+$"; "valid")

Extract all uppercase sequences

=REGEX(A1; "[A-Z]+"; "")

Extract the last number

=REGEX(A1; ".*?(\d+)$"; "$1")

Empty replacement → extraction mode

No match → returns original text

Unless pattern anchored with ^…$.

Capturing groups use $1, $2, etc.

Global flag “g” required for multiple replacements

Multi-line mode “m” affects ^ and $

text is a number → converted to text

pattern errors → Err:509

REGEX of an error propagates the error

Unicode support varies by engine

Greedy vs. lazy quantifiers matter

• .* → greedy
• .*? → lazy

• Use REGEX for complex parsing and cleanup
• Use capturing groups for structured extraction
• Use lazy quantifiers to avoid overmatching
• Use anchors for validation
• Use TRIM and CLEAN before applying REGEX
• Combine REGEX with TEXTBEFORE/TEXTAFTER for hybrid parsing
• Use global flag “g” for multi-match replacement

• Use TEXTBEFORE and TEXTAFTER for simple delimiter parsing
• Use SEARCH and FIND for positional extraction
• Use LEFT, RIGHT, MID for manual slicing
• Use SUBSTITUTE for simple replacements
• Use SPLIT for array-based decomposition

By mastering REGEX and its companion functions, you can build extremely powerful, flexible, and expressive text‑processing workflows in LibreOffice Calc.