Regular Expression Formulas in Google Sheets
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, "[0-9]{4}-[0-9]{2}-[0-9]{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.