Create advanced Works with: Any AI

Regular Expression Formulas in Google Sheets

Table output Has example

Extract and validate text patterns.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Write regular expression formulas for Google Sheets. Text examples and what to extract or validate: [TEXT_EXAMPLES_AND_GOALS]. Sample cells (fake): [SAMPLE_CELLS].

Provide, for each goal, a table row: Goal | Formula | Pattern explained part by part | Test inputs and outputs.
Include: REGEXEXTRACT for the first match (=REGEXEXTRACT(A2, "\d{4}-\d{2}-\d{2}")); extracting all matches with a pattern that returns an array; REGEXMATCH for validation (=REGEXMATCH(A2, "^[^@\s]+@[^@\s]+\.[a-zA-Z]{2,}$")) with a warning that email validation by pattern is only a rough check; REGEXREPLACE for cleanup (remove non-digits, collapse spaces, mask parts of a value); combining with IFERROR for no match; case-insensitive flags (?i); capturing groups to extract part of a match; and wrapping in ARRAYFORMULA for whole columns.

Add a cheat sheet of pattern elements (anchors, character classes, quantifiers, groups, escaping) with one example each, advice to test patterns on a variety of inputs including edge cases, and warnings about greedy matching and overly strict patterns. For Excel users, describe alternatives (newer functions or Power Query) without assuming availability. Include a validation set of ten inputs with expected results.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Goal | Formula | Test
Extract a date | =REGEXEXTRACT(A2, "[0-9]{4}-[0-9]{2}-[0-9]{2}") | "Invoice 2026-10-15 paid" -> 2026-10-15
Rough email check | =REGEXMATCH(A2, "^[^@\s]+@[^@\s]+\.[a-zA-Z]{2,}$") | "[email protected]" TRUE; "a@b" FALSE
Digits only | =REGEXREPLACE(A2, "[^0-9]", "") | "(555) 010-0142" -> 5550100142
Case-insensitive | =REGEXMATCH(A2, "(?i)refund") | "REFUND request" TRUE
Whole column: =ARRAYFORMULA(REGEXREPLACE(A2:A, "[^0-9]", "")). Pattern checks are rough; test edge cases such as empty cells, very long text, and unusual characters. Cheat sheet: ^ start, $ end, [0-9] a digit, {4} four times, + one or more, ( ) a group. Beware greedy matching and patterns that are stricter than real data.

• regex